Omg FINALLY. It's taken me ages to work this one out. Even a couple of my other trusty youtubers didn't quite have a solution that worked for me. Thank you!
I did something similar. I created a column. I nasty huge big if else that said if 0 - 10 = "0-10" elseif 11 - 20 = "11-20" so on so on. Then I made a slicer on that column and it worked just fine. Filtered based on "ranges".
Hi Patrick, nice solution actually. Many users would like to have different usability. They want a slicer with (continuous) numerical values so they can choose whatever number interval ( so... between 1234 and 69493839). That is not possible at the moment and I think this is what most people would like to have.
Brilliant Patrick.I have a question for you I have 4 measures like sales,Trans Margin,%TRans Margin and %Contribution and user wants these measures as slicers with in single column like Dynamic column selection. Kindly do needful on this.
I had a similar problem where I needed to create a dynamic filter without using the filter pane. An alternate solution was to use a What if Parameter and then SelectedValue() to filter based off what the user typed in. Overall very similar process but the end users can now filter by whatever value their heart desires.
How could this be adapted for a Matrix? The issue I am having is that the totals of the matrix are still counting the blank rows. I tried to filter out the blank rows with SUMX but this becomes really slow. Is there a more performant way to have the totals be correct when filtering the matrix by a range bucket?
Hi Milena I would modify the disconnected table to have two value columns let's say 'Value from' & 'Value to' and then in the measure create two variables VAR _filtervaluefrom = SELECTEDVALUE ( 'Measure Filter'[Value from] ) and VAR _filtervalueto = SELECTEDVALUE ( 'Measure Filter'[Value to] ). Last thing is to amend the SWITCH statement in the first line as follows AND ( _currentsalesamount >= _filtervaluefrom , _currentsalesamount
Hello! Great Solution, What about the Total of filtered values? It was not there. Kindly explain How can we have Totals of filtered values in the table?
Hi Patrick Thanks for all the explanations and videos They are a huge help for me a question: I want to perform a calculation that is conducted according to an index table For example, if the employee's efficiency is between 85% and 95%, reward him with $ 70 If the efficiency of an employee is between 95% and 100% $ 100 this month My efficiency is a measure I know it can be done with the switch function But the index and amounts change once a year so it is easier to maintain in the table than in the function within the report Is there a way ?????
hi Patrick I have a slicer which has name of all salesperson however i need to display the graph or matrix only for the selected salesperson, that is when i choose a salesman from the slicer it should prompt for a password and then validate that and display data. Is this possible .. thanking you
Great video! I have implemented something similar. 😊 I note when you apply BLANK() to any measure, that you then do not get a total in a matrix visual ... can you amend the measure so that you can? ie. possibly using a dax generated table instead with filter rather than switch true??
i have one question, i have two or more tables with one column name like material_number, product_number,material_code these are all columns refering same data, how can i create slicer for this
Hi In my dashboard have one table, one card , one slicer is there and my question is when i am selecting the slicer no need to change card only change table is it possible?, How ?.
@Patrick, This is helpful. Whenever I hit a roadblock I get some or other guidance from your blogs. But, today I have a unique ask. I have created the slicer panel with bookmarks following your blogs. Now, the user requirement is that "how to close the slicer panel automatically after selection"? To simplify, when user's cursor remain over the slicer panel it will stay pop out and give the facility to select whatever the user wants to select, but when he is done and hovers out of the slicer pane, the slicer pane should should disappear. Can this be done?
Thank you for this video, can you please make a video on how can we create a Dax table derived from one of our existing table in PBI; and the dax table is getting filtered dynamically by using some filter condition like ColumnValue in selectedvalue(column). Please guide alternative approaches
Hey Patrick, I have this situation: let's say I have sales by country (let's say, US, Canada, Spain), but 1 column per country. I want to be able to filter which country I need see in the table, but being able to see ech country individually, like: select US, have US column, if I select US and Canada, I'd like to see both columns with each country sales, not a total consolidated in 1 column. I have tried 2 solutions for this filter, none of them works for me. I tried a measure with a Switch function to show the sum of my selection, however, it shows all the countries data agregated in 1 single column. Other solution could be Unpivot columns, to have it all in the same column, then filter, but I have a huge model (MM of rows coming from DataBricks), so it's not a good option. Any suggestion how to handle this? 🤔 Thanks,
Hi Adam and Patrick. I have one question in dax Can we do dynamic measures selection in power BI. - users the option of choosing a specific measure for display in visualization?
This answers a problem I'm trying to solve right now so amazing timing! Thanks Patrick. But I have another related question/problem: How would you slice by a DIFFERENT measure (not the same measure used in the table/visual)? So going back to your example, what if you wanted to slice your total sales by your total quantity or your total freight (might not make any sense business or analytically-wise but I hope the question makes sense)?
Thanks Patrick. How do you handle totals in this? Also, if I have to keep both the measures with and without filters and do not show blanks, how do I do that?
Great and simple solution. I was just looking similar solution for power pivot, Excel simple model: how to exclude 0 (zero amount). This solution could be adjusted how to exclude 0 (zero amount)?
The animations are getting better and better.. Patrick Patrick Patrick Patrick Patrick Cool solution. Could probably use calculation groups to make this work across any measure?
I had the same thought, and now I'm going down a rabbit hole figuring out if I can not only use a CG, but whether I could script the CG in C#. I guess though, there might be some scenarios where you don't want to apply the filter on all measures. If you had a table that showed Price, Cost and Profit and you wanted to filter by Profit < X, then you wouldn't want to then filter the other measures too... isn't gonna stop me going down this rabbit hole though!
Hey Patrick, I have one question. I’ve done color formatting based on rules for a map but I want to show the color only for the selected criterion and the other should be shown with some default color
Hi Guys, really like your videos, really helpfull. Would you perhaps know if it's possible to rotate (90 degrees counter clockwise) the headers in a matrix?
You'll have to create a new measure like this: Measure 2 = SUMX(SUMMARIZE(table_x,table_x'[Product Name],"_1",[Total Sales Amount Filters]),[_1]) and use this measure in the table instead of the measure called Total Sales Amount Filters which Patric used in the video. This will give you the grand totals when using the slicer.
I want to use slicer selection as an input parameter for a stored procedure. I know how to use parameter in a dynamic execute query but I want to use slicker selection to be fed into stored procedure input parameter. Please please please someone help me here? I have been searching everywhere and did not get any solution.
Hi Patrik,I really loved your videos. I have a question I have two different calendar tables that used for different visuals in the same page, 1st calendar tables used in slicer for one visual 2nd calendar table used as another slicer for second visual And both the calendar tables have no Relationship. suppose if i selected August month in 1 st slicer, in 2nd slicer should also automatically select Aug month, Is there a way to do that. Please help Thank you
You'll have to create a new measure like this: Measure 2 = SUMX(SUMMARIZE(table_x,table_x'[Product Name],"_1",[Total Sales Amount Filters]),[_1]) and use this measure in the table instead of the measure called Total Sales Amount Filters which Patric used in the video. This will give you the grand totals when using the slicer.
Nice niceness! However... If I am working from Azure Analysis Services and use Embedded (which does not support a composite model) how could this work?
This can be done by creating the same form of a table in a different source and add that as a seperate query. For more on how to do it, check documentation.
Hi Adam and Patrick ,I have an HR data by which I have created 3 matrix in power Bi, 1 for New employee ,2 for total number of employee 3 for attrition , all three matrix have departments in rows and months in columns. Now for 2 matrix I have taken running total that add last month value and newly recruited employee in present month using calculate function ,this works fine but when I tried to subtract attrition values from above running total it start giving wrong result , Ex :-If there is 1 attrition in any department in current month then my formula start subtracting 1 from each department and hence produce wrong result. please help in writing correct Dax function. Plz guys help me with this measure
would recommend the official forums as you can then also share screenshots of your model and tables. Think you could get this cleared up in an hour or so.
I think you guys never worked in logistics. Aging buckets/bins are a rave there, and I have solved this issue a year ago just in the same way shown here :) Aging reports are a usual thing in accounting, too - so this method applies there.
Hi Patrick Based on "today date" I want to previous quarter end date. (I have no date table) Example Today date 19/05/2021 I want to previous quarter end date For example 31/03/2021. Please help me Patrick
You could write a measure (using SUMX(...)), that could sum the filtered values only but still show the Total. The drawback is that you do need to know that granularity level to which you apply the logic.
@@arklur3193 You'll have to create a new measure like this: Measure 2 = SUMX(SUMMARIZE(table_x,table_x'[Product Name],"_1",[Total Sales Amount Filters]),[_1]) and use this measure in the table instead of the measure called Total Sales Amount Filters which Patric used in the video. This will give you the grand totals when using the slicer.
That Scott guy sounds high-maintenance. 😉
I enjoy the way you present content over the solutions. Thank you for making life easier.
Omg FINALLY. It's taken me ages to work this one out. Even a couple of my other trusty youtubers didn't quite have a solution that worked for me. Thank you!
This whole day I've been cracking my head over it!!! FINALLY !!!!!
I came with a similar solution some time ago and I was very proud of myself and I thought "This should be a Guy In a Cube video"
Whenever I hear that Patrick is saying 'yoooooo!' , I know there will be another cool thing to learn about Power BI! Thanks man!
Wow - simply superb Patrick!! Amazed with your idea and solution..
Thanks Scott for raising the hard question 😂
I did something similar. I created a column. I nasty huge big if else that said if 0 - 10 = "0-10" elseif 11 - 20 = "11-20" so on so on. Then I made a slicer on that column and it worked just fine. Filtered based on "ranges".
Awesome solution, great work Patrick.
Exactly what I wanted, this is great 👍
Hi Patrick, nice solution actually.
Many users would like to have different usability. They want a slicer with (continuous) numerical values so they can choose whatever number interval ( so... between 1234 and 69493839).
That is not possible at the moment and I think this is what most people would like to have.
Whaaaat! That was genius!! Love this channel. Grettings from Colombia
Brilliant Patrick.I have a question for you I have 4 measures like sales,Trans Margin,%TRans Margin and %Contribution and user wants these measures as slicers with in single column like Dynamic column selection. Kindly do needful on this.
I had a similar problem where I needed to create a dynamic filter without using the filter pane. An alternate solution was to use a What if Parameter and then SelectedValue() to filter based off what the user typed in. Overall very similar process but the end users can now filter by whatever value their heart desires.
Hi justin, have you posted this in any site? I would like to watch in detail! Thanks in advance!
@@ACastilloYT Sorry, No I don't have any posts anywhere. I am just a fan of #GuyInACube.
Will Smith! This is the only method that has worked for me. This really really helped me out! Thank you for this my brother!
Very elegant, thanks for sharing!
How could this be adapted for a Matrix? The issue I am having is that the totals of the matrix are still counting the blank rows. I tried to filter out the blank rows with SUMX but this becomes really slow. Is there a more performant way to have the totals be correct when filtering the matrix by a range bucket?
Excellent solution 👍 Bravo 👏
Thank you for the great video, but I have one more question. While choosing for example
Hi Milena
I would modify the disconnected table to have two value columns let's say 'Value from' & 'Value to' and then in the measure create two variables
VAR _filtervaluefrom = SELECTEDVALUE ( 'Measure Filter'[Value from] ) and
VAR _filtervalueto = SELECTEDVALUE ( 'Measure Filter'[Value to] ).
Last thing is to amend the SWITCH statement in the first line as follows
AND ( _currentsalesamount >= _filtervaluefrom , _currentsalesamount
Thanks a lot !!!
Hello! Great Solution, What about the Total of filtered values? It was not there. Kindly explain How can we have Totals of filtered values in the table?
Workaround would be to add a card to show the total for summarized measure.
You can have if has one valve and use the original measure
## Total Sales Amount Filtered =
VAR SlicerSelection =
SELECTEDVALUE( 'Slicer Table'[Value] )
VAR CurrentSalesAmount = [Sales Amount]
VAR Result =
IF(
ISINSCOPE( 'Product'[Product Name] ) ,
SWITCH(
TRUE() ,
CurrentSalesAmount
Absolutely brilliant!
Hi Patrick Thanks for all the explanations and videos
They are a huge help for me
a question:
I want to perform a calculation that is conducted according to an index table
For example, if the employee's efficiency is between 85% and 95%, reward him with $ 70
If the efficiency of an employee is between 95% and 100% $ 100 this month
My efficiency is a measure
I know it can be done with the switch function
But the index and amounts change once a year so it is easier to maintain in the table than in the function within the report
Is there a way ?????
hi Patrick
I have a slicer which has name of all salesperson however i need to display the graph or matrix only for the selected salesperson, that is when i choose a salesman from the slicer it should prompt for a password and then validate that and display data. Is this possible ..
thanking you
Great video! I have implemented something similar. 😊
I note when you apply BLANK() to any measure, that you then do not get a total in a matrix visual ... can you amend the measure so that you can? ie. possibly using a dax generated table instead with filter rather than switch true??
Good question. Maybe if you replace the Blank() with 0 and in the filter pane add: the created metric > 0. Should do the trick
Have you found solution how to handle with totals?
Hi Patrick you are more efficient to create the filter table using Enter data it should be some way to script it
i have one question, i have two or more tables with one column name like material_number, product_number,material_code these are all columns refering same data, how can i create slicer for this
Hi
In my dashboard have one table, one card , one slicer is there and my question is when i am selecting the slicer no need to change card only change table is it possible?, How ?.
@Patrick, This is helpful. Whenever I hit a roadblock I get some or other guidance from your blogs. But, today I have a unique ask. I have created the slicer panel with bookmarks following your blogs. Now, the user requirement is that "how to close the slicer panel automatically after selection"? To simplify, when user's cursor remain over the slicer panel it will stay pop out and give the facility to select whatever the user wants to select, but when he is done and hovers out of the slicer pane, the slicer pane should should disappear. Can this be done?
Thank you for this video, can you please make a video on how can we create a Dax table derived from one of our existing table in PBI; and the dax table is getting filtered dynamically by using some filter condition like ColumnValue in selectedvalue(column). Please guide alternative approaches
Hey Patrick, I have this situation: let's say I have sales by country (let's say, US, Canada, Spain), but 1 column per country. I want to be able to filter which country I need see in the table, but being able to see ech country individually, like: select US, have US column, if I select US and Canada, I'd like to see both columns with each country sales, not a total consolidated in 1 column.
I have tried 2 solutions for this filter, none of them works for me. I tried a measure with a Switch function to show the sum of my selection, however, it shows all the countries data agregated in 1 single column. Other solution could be Unpivot columns, to have it all in the same column, then filter, but I have a huge model (MM of rows coming from DataBricks), so it's not a good option.
Any suggestion how to handle this? 🤔
Thanks,
Nice move !!! thank you
Thanks Scott 😀👍🙏
Hi Adam and Patrick.
I have one question in dax
Can we do dynamic measures selection in power BI.
- users the option of choosing a specific measure for display in visualization?
Hi Patrick, it's works very well with just two columns, I've tried in a table with several columns and I think I would need to relate tables right?
This answers a problem I'm trying to solve right now so amazing timing! Thanks Patrick. But I have another related question/problem: How would you slice by a DIFFERENT measure (not the same measure used in the table/visual)?
So going back to your example, what if you wanted to slice your total sales by your total quantity or your total freight (might not make any sense business or analytically-wise but I hope the question makes sense)?
Not sure if I understood correctly, but from what I can see I’d use a calculation group.
Thanks Patrick. How do you handle totals in this? Also, if I have to keep both the measures with and without filters and do not show blanks, how do I do that?
Great and simple solution. I was just looking similar solution for power pivot, Excel simple model: how to exclude 0 (zero amount).
This solution could be adjusted how to exclude 0 (zero amount)?
The animations are getting better and better..
Patrick
Patrick
Patrick
Patrick
Patrick
Cool solution. Could probably use calculation groups to make this work across any measure?
I had the same thought, and now I'm going down a rabbit hole figuring out if I can not only use a CG, but whether I could script the CG in C#. I guess though, there might be some scenarios where you don't want to apply the filter on all measures. If you had a table that showed Price, Cost and Profit and you wanted to filter by Profit < X, then you wouldn't want to then filter the other measures too... isn't gonna stop me going down this rabbit hole though!
Hi Patrick
This one doesn't work for me for some reason..
stuck!!
Thank you. Since no relationship, it doesn't work with drill through to the detail page.
Hey Patrick, I have one question. I’ve done color formatting based on rules for a map but I want to show the color only for the selected criterion and the other should be shown with some default color
Hi Guys, really like your videos, really helpfull. Would you perhaps know if it's possible to rotate (90 degrees counter clockwise) the headers in a matrix?
Can you do a video please do a video on market data analysis. How can we compare different employees with their respective market data.
@Patrick: How to enable the grand total in this case (for all amounts shown)?
You'll have to create a new measure like this: Measure 2 = SUMX(SUMMARIZE(table_x,table_x'[Product Name],"_1",[Total Sales Amount Filters]),[_1]) and use this measure in the table instead of the measure called Total Sales Amount Filters which Patric used in the video. This will give you the grand totals when using the slicer.
@@Darko8997 Thx. I had a suspicion on SUMX, you confirmed it. Great!
I want to use slicer selection as an input parameter for a stored procedure. I know how to use parameter in a dynamic execute query but I want to use slicker selection to be fed into stored procedure input parameter. Please please please someone help me here? I have been searching everywhere and did not get any solution.
how do you make an overall risk visual of conservative, moderate and aggressive visuals?
Hi Patrik,I really loved your videos. I have a question
I have two different calendar tables that used for different visuals in the same page,
1st calendar tables used in slicer for one visual
2nd calendar table used as another slicer for second visual
And both the calendar tables have no Relationship.
suppose if i selected August month in 1 st slicer, in 2nd slicer should also automatically select Aug month, Is there a way to do that.
Please help
Thank you
Wow! Amazing :-)
Hi Patrick, do you have any video about how to filter calculated columns? I am getting crazy with this... HEEEELLPP!!!!
Same trick i used earlier in one of my dashboard
Hello Patrick , how can we add multiple slicers using the same Measure column?
Hello Patrick, How can we get the totals on the Visuals? I'm trying it on a matrix
oh yeah !!
Has anyone done this with a date? Basically was something effective on a certain date or not?
Hi Adam, Excellent solution, how can i make sure that i get the grand total also to be displayed ?
Add a test using hasonefilter() or something similar and if it returns false then return the total.
You'll have to create a new measure like this: Measure 2 = SUMX(SUMMARIZE(table_x,table_x'[Product Name],"_1",[Total Sales Amount Filters]),[_1]) and use this measure in the table instead of the measure called Total Sales Amount Filters which Patric used in the video. This will give you the grand totals when using the slicer.
## Total Sales Amount Filtered =
VAR SlicerSelection =
SELECTEDVALUE( 'Slicer Table'[Value] )
VAR CurrentSalesAmount = [Sales Amount]
VAR Result =
IF(
ISINSCOPE( 'Product'[Product Name] ) ,
SWITCH(
TRUE() ,
CurrentSalesAmount
Brilliant! Just remember to keep using your genius for good and not evil Patrick. That's how Lex Luthor got started....
could same result have been achieved using calculated groups?
Hi Patrick, I think adding a new column with an IF statement in Power Query could provide the same solution.
Brilliant!
Brilliant
This is another embodiment of the old adage in the programming world: "Every problem can be solved by adding another layer of indirection."
Awesome 👍👍
Thank you Scott 😊
Nice niceness! However... If I am working from Azure Analysis Services and use Embedded (which does not support a composite model) how could this work?
This can be done by creating the same form of a table in a different source and add that as a seperate query. For more on how to do it, check documentation.
Hi Adam and Patrick ,I have an HR data by which I have created 3 matrix in power Bi, 1 for New employee ,2 for total number of employee
3 for attrition , all three matrix have departments in rows and months in columns.
Now for 2 matrix I have taken running total that add last month value and newly recruited employee in present month using calculate function ,this works fine but when I tried to subtract attrition values from above running total it start giving wrong result ,
Ex :-If there is 1 attrition in any department in current month then my formula start subtracting 1 from each department and hence produce wrong result.
please help in writing correct Dax function. Plz guys help me with this measure
would recommend the official forums as you can then also share screenshots of your model and tables. Think you could get this cleared up in an hour or so.
Hello Patrick, I’ve a requirement and it’s complex to explain on chat. Let me know how to connect with you to discuss on this.
I think you guys never worked in logistics. Aging buckets/bins are a rave there, and I have solved this issue a year ago just in the same way shown here :) Aging reports are a usual thing in accounting, too - so this method applies there.
Hi Patrick
Based on "today date"
I want to previous quarter end date.
(I have no date table)
Example
Today date 19/05/2021
I want to previous quarter end date
For example
31/03/2021.
Please help me Patrick
Use switch funcation… ping me i will explain you
Use this DAX
LastQtrDate =
---Get Last Qtr End Date By Today Date
VAR MinusMonth =
SWITCH (
TRUE (),
FORMAT ( TODAY (), "MMM" ) IN { "JAN", "APR", "JUL", "OCT" }, 1,
FORMAT ( TODAY (), "MMM" ) IN { "FEB", "MAY", "AUG", "NOV" }, 2,
FORMAT ( TODAY (), "MMM" ) IN { "MAR", "JUN", "SEP", "DEC" }, 3
)
RETURN
EOMONTH ( TODAY (), - MinusMonth )
The major drawback is that you hide the Total.
You could write a measure (using SUMX(...)), that could sum the filtered values only but still show the Total. The drawback is that you do need to know that granularity level to which you apply the logic.
@@arklur3193 Yeah I can't think of a way to do this (calculate total) without having to define the grain beforehand
@@arklur3193 exactly
@@arklur3193 You'll have to create a new measure like this: Measure 2 = SUMX(SUMMARIZE(table_x,table_x'[Product Name],"_1",[Total Sales Amount Filters]),[_1]) and use this measure in the table instead of the measure called Total Sales Amount Filters which Patric used in the video. This will give you the grand totals when using the slicer.
Where's your total, Patrick? ;P
But where is the Total Patric? And how about a Remainder Row? And could you filter also by TOP N? 🤣😎
Ahh, that was his problem. He was using bing.