Thank you so much for this! Can you please show how we can sum multiple categories in each slicer for comparison? For example, the sum of sales in California and Washington, vs the sum of sales in 5 other states.
Just finished the video and solved the question ehhehe make these other 2 Measures Mind the tables and column names Sales Category Comparisson First Sales = CALCULATE( SUM(sales[Sales Value]), FILTER( products, products[Product_Category] in { SELECTEDVALUE(_CategoryFist[Product_Category]) } ) ) Sales Category Comparisson Second Sales = CALCULATE( SUM(sales[Sales Value]), FILTER( products, products[Product_Category] in { SELECTEDVALUE(_CategorySecond[Product_Category]) } ) ) Now you can calculate difference between them and fixed all the problems rly easy :D
If you want have it that when you dont select something to use the all value instead of blank you do like **Mind tables and column names Sales Category Comparison First Sales = VAR SelectectFilter = CALCULATE( SUM(sales[Sales Value]), FILTER( products, products[Product_Category] in { SELECTEDVALUE(_CategoryFist[Product_Category]) } ) ) RETURN if( ISBLANK(SelectectFilter),SUM(sales[Sales Value]),SelectectFilter)//
I made a measure that subtracts the selected value of the first selection (similar to: calculate [total sales], filter(categories, categories[category name] in selectedvalue('firstselection'[category name])) from the selected value of the second selection, and drags this measure to a table to show the variance between the two.
Great Video ❤❤!! This was exactly my requirement and now I have achieved that with the help of this video. Just one more thing I would like to know, I have 4 Visuals on a page and 3 of them I have created using measures as explained in the video, but one of them is a Table visual which is utilizing only the fields from the source table and no measure is used in that table visual, so that table visual is not filtering out based on the slicer selection, can you please help with that, it would be really helpful. Thanks 😊
Useful one, but my question here is if we are creating a dashboard in page where we are using "category name" as a slicer too and then I want to use it, then according to the slicer I don't want to change this slicers, how to make it possible
Great! i wonder if its possible to create a filter out a filter, lets say one main filter in which you select the measure to compare and based on this selection Filters A and B can be compared
Is there a way to show the intersection of the categories? For example, On the first slicer selection show all products, on the second slicer selection add the Products which are common for the Slicer 1 and 2 categories. If there are no Products that belong to both categories show blank?
Hey Felipe, yes it is possible! Though the logic gets more complicated with each additional slicer. If you add a 3rd slicer, the measure for filtering the slicers will look like this: Category Selection Filter = IF( SELECTEDVALUE('First Selection'[Category Name]) = SELECTEDVALUE('Second Selection'[Category Name]) || SELECTEDVALUE('First Selection'[Category Name]) = SELECTEDVALUE('Third Selection'[Category Name]) || SELECTEDVALUE('Second Selection'[Category Name]) = SELECTEDVALUE('Third Selection'[Category Name]), 0, 1 ) Also remember to add the 3rd slicer selection to the measure used for visualization.
@@BIElite Maybe this condition could be expressed more briefly and in a general form by comparing a COUNT of {} of all SELECTEDVALUES with a DISTINCTCOUNT of the same thing, or alternatively an INTERSECTION of two sets. Will give it a try when the need for this pattern arises in our dashboards, otherwise great pattern.
I created multiple queries from two columns of small table, made it unique & created relationship. Intention is to create relational filter but no didn't work ?
Hello, very helpful, thank you very much - I am wondering about one thing. I am using the technique in multiple charts on one page to compare stats of two countries (out of the global list of about 250). In order to keep the the page readable, I would need the used colors uniform and fixed to the first/second selected country acros all visuals on the page. However, the colors reset after each change of the selection. Can you think of a way to prevent this behavior and have two chosen colors stick to the first/second choice respectively independent of their values?
I figured it out now and successfully applied it in this covid dashboard lnkd.in/d4PCCmy - the second page of the report - side by side comparison is mainly based on the technique presented in your video - thanks again!
Hi, how to select only one option in two different filters? example: In Filttro 1 it has the options (A, B ,C) and in Filttro 2 it has the options (D,E,F). So, how to select only 1 option among all options (A,B,C,D,E,F)?
hello, I want to make this but for the same measure. For example I want to create the the total sales of a single category but I want to compare the sales by year. In this case the two slicers that I need are the slicer of year number and my x-axis is going to be the month. I did what is in the video but instead of using the category names in the slicer I used the year but that is not working. Does anyone have the solution for this?
@BI Elite: I have the same problem. does it work only with single select? I have the same requirement wherein I have one single select and one multi select slicer for the same category and I need my chart to be filtered with all the values selected from both slicers. Is this possible? Please reply.
Hi Parker. Great Video!! like always, Thank you very much, one small request, can you please kindly remove the Sadow on your mouse pointer. I love your videos, but the shadow, I really don't know how many other people feel the same but it very much discomforting the eye. Maybe it's just me. Will be really grateful if you can remove it. At end of the day, it's your channel. :) Thank you
This is useful for making direct A vs B comparisons. Imagine you have 100 employees and you want to compare Employee A vs Employee B. With a single slicer, the default will show all 100 employees. You can always select 2 employees, but it'll be a mess when you undo the selection. With the method from this video, you are forcing Power BI (and the user) to only show 2 categories at a time, no more no less.
Fantastic. I went through so much stuff but this is a jackpot! thank you for this✌
I've been binge watching all your videos, and the learnings are just so much. Thank you
BRILLIANT!! I have been looking for this for so long now! Thank you for the great explanation!
Thank you so much for this! Can you please show how we can sum multiple categories in each slicer for comparison? For example, the sum of sales in California and Washington, vs the sum of sales in 5 other states.
still dindt find this anywhere xD
Just finished the video and solved the question ehhehe make these other 2 Measures
Mind the tables and column names
Sales Category Comparisson First Sales =
CALCULATE(
SUM(sales[Sales Value]),
FILTER(
products,
products[Product_Category]
in
{
SELECTEDVALUE(_CategoryFist[Product_Category])
}
)
)
Sales Category Comparisson Second Sales =
CALCULATE(
SUM(sales[Sales Value]),
FILTER(
products,
products[Product_Category]
in
{
SELECTEDVALUE(_CategorySecond[Product_Category])
}
)
)
Now you can calculate difference between them and fixed all the problems rly easy :D
If you want have it that when you dont select something to use the all value instead of blank you do like
**Mind tables and column names
Sales Category Comparison First Sales =
VAR SelectectFilter =
CALCULATE(
SUM(sales[Sales Value]),
FILTER(
products,
products[Product_Category]
in
{
SELECTEDVALUE(_CategoryFist[Product_Category])
}
)
)
RETURN if( ISBLANK(SelectectFilter),SUM(sales[Sales Value]),SelectectFilter)//
@@welcometomathy Can you please help me to select one value from first slicer and all values from second slicer. Please help me write DAX function?
Clear-cut presentation and DAX use! I'm so glad I stumbled upon your video. Subscribed!
Awesome! You saved to me lots of learning time by publishing this clear and well-described solution.
looking for exact scenario , thanks
This is awesome, thank you! Is there a way to create a measure that displays the variance of the two selections?
This is what I need too! Please suggest if you found something out on this!!
Would like to know about this too Thanks!
I made a measure that subtracts the selected value of the first selection (similar to: calculate [total sales], filter(categories, categories[category name] in selectedvalue('firstselection'[category name])) from the selected value of the second selection, and drags this measure to a table to show the variance between the two.
@@BellaLuGuzheng Care to put here how you did it? I can't seem to be able to do it.
Great Video ❤❤!! This was exactly my requirement and now I have achieved that with the help of this video. Just one more thing I would like to know, I have 4 Visuals on a page and 3 of them I have created using measures as explained in the video, but one of them is a Table visual which is utilizing only the fields from the source table and no measure is used in that table visual, so that table visual is not filtering out based on the slicer selection, can you please help with that, it would be really helpful. Thanks 😊
but i don't have measure to use in my Gantt chart so in this case how to proceed. appreciate your response
If we enable multiple selection in the second slicer, will it still work? Or what should be the change in the dax measure?
Useful one, but my question here is if we are creating a dashboard in page where we are using "category name" as a slicer too and then I want to use it, then according to the slicer I don't want to change this slicers, how to make it possible
Excellent explanation. Thank you very much!
I've been looking that for so long.
Great! i wonder if its possible to create a filter out a filter, lets say one main filter in which you select the measure to compare and based on this selection Filters A and B can be compared
great work..this is in my current requirement but i couldn't achieve. Now i will implement the same..thank you
Exactly what i am looking for. Thank you!
how about multiple second scenario selection?
Is there any way, I will select a category name and in table gets brand name and if I select it should get sub category name in same table
Is there a way to show the intersection of the categories? For example, On the first slicer selection show all products, on the second slicer selection add the Products which are common for the Slicer 1 and 2 categories. If there are no Products that belong to both categories show blank?
How did you calculate total sales? Thank you
Super helpful! great job & Thanks for sharing!
Is it possible get the same scenario where first slicer is single selection and the other slicer with multiple selection ?
Great tip! Thanks!! I will give it a try. However, is it possible to use a similar approach to more than 2 slicers?
Hey Felipe, yes it is possible! Though the logic gets more complicated with each additional slicer. If you add a 3rd slicer, the measure for filtering the slicers will look like this:
Category Selection Filter =
IF(
SELECTEDVALUE('First Selection'[Category Name]) = SELECTEDVALUE('Second Selection'[Category Name])
|| SELECTEDVALUE('First Selection'[Category Name]) = SELECTEDVALUE('Third Selection'[Category Name])
|| SELECTEDVALUE('Second Selection'[Category Name]) = SELECTEDVALUE('Third Selection'[Category Name]),
0,
1
)
Also remember to add the 3rd slicer selection to the measure used for visualization.
@@BIElite Amazing! Thanks.
@@BIElite Maybe this condition could be expressed more briefly and in a general form by comparing a COUNT of {} of all SELECTEDVALUES with a
DISTINCTCOUNT of the same thing, or alternatively an INTERSECTION of two sets. Will give it a try when the need for this pattern arises in our dashboards, otherwise great pattern.
I created multiple queries from two columns of small table, made it unique & created relationship. Intention is to create relational filter but no didn't work ?
Thats really awesome Parker !! Thanks for sharing :)
Hello, very helpful, thank you very much - I am wondering about one thing. I am using the technique in multiple charts on one page to compare stats of two countries (out of the global list of about 250). In order to keep the the page readable, I would need the used colors uniform and fixed to the first/second selected country acros all visuals on the page. However, the colors reset after each change of the selection. Can you think of a way to prevent this behavior and have two chosen colors stick to the first/second choice respectively independent of their values?
I figured it out now and successfully applied it in this covid dashboard lnkd.in/d4PCCmy - the second page of the report - side by side comparison is mainly based on the technique presented in your video - thanks again!
Hi, how to select only one option in two different filters? example: In Filttro 1 it has the options (A, B ,C) and in Filttro 2 it has the options (D,E,F). So, how to select only 1 option among all options (A,B,C,D,E,F)?
hello, I want to make this but for the same measure. For example I want to create the the total sales of a single category but I want to compare the sales by year. In this case the two slicers that I need are the slicer of year number and my x-axis is going to be the month. I did what is in the video but instead of using the category names in the slicer I used the year but that is not working. Does anyone have the solution for this?
That was brilliant! Thanks a lot!
Report is interacting with main category only not with other slicer, i am creating bar chart
Hi @BI Elite, will this work for Drop-down slicer?
Can you sync existing slicers to these new slicers?
what if we want compare more than 2 ? like 3 or as many as we want?
Very cool! Thank you!
Thanks for watching, Michael!
Nice one!
Thanks Erica!
if we apply one categorgy at one slicer can you explain it
Thank you so much BI Elite. I am your fine
Just great !!. Thanks for sharing
Thank you soo much
Awesome, thanks
Does it only work with single select?
@BI Elite: I have the same problem. does it work only with single select? I have the same requirement wherein I have one single select and one multi select slicer for the same category and I need my chart to be filtered with all the values selected from both slicers. Is this possible? Please reply.
Hi Parker.
Great Video!! like always, Thank you very much,
one small request, can you please kindly remove the Sadow on your mouse pointer.
I love your videos, but the shadow, I really don't know how many other people feel the same but it very much discomforting the eye. Maybe it's just me.
Will be really grateful if you can remove it. At end of the day, it's your channel. :)
Thank you
Sorry, but I can't see the advantage in having the chart controlled by 2 slicers instead than by one. Thank you.
This is useful for making direct A vs B comparisons. Imagine you have 100 employees and you want to compare Employee A vs Employee B. With a single slicer, the default will show all 100 employees. You can always select 2 employees, but it'll be a mess when you undo the selection. With the method from this video, you are forcing Power BI (and the user) to only show 2 categories at a time, no more no less.
Thanks@@BIElite , I got it.
No problem! Thanks for watching
Gr8
where the hell `total sales` measure came from!!!
lifesaver. TY!