Forum Discussion
Help with Filters and Slicers
All,
I currently have a measure Total Orders:= DISTINCTCOUNT( 'Orders'[Order ID]) that gives me the total number of orders in the fact table below. I have a slicer built off field marked 1. in the screenshot which has a number of categories. This successfully filters all visuals on a page using the Total Orders measure.
My issue is that some of the categories from that slicer have only specific Payors applicable and should be filtered out when selected. These payors are grouped in the calculated column marked 2. I tried modifying my measure to include this criteria using
Total Orders Modified:=
SWITCH(
TRUE(),
SELECTEDVALUE( 'Order Group'[Order Group] ) = "Category A", CALCULATE( DISTINCTCOUNT( 'Orders'[Order ID] ), AND( 'Payor'[Payor Groups] = "X", 'Payor'[Payor Groups] = "Y" ) ),
SELECTEDVALUE( 'Order Group'[Order Group] ) = "Category B", CALCULATE( DISTINCTCOUNT( 'Orders'[Order ID] ), 'Payor'[Payor Groups] = "Z",
DISTINCTCOUNT( 'Orders'[Order ID]
)
The modified measure works most of the time, but when placed against a set of items, instead of giving the breakdown total for each item, it gives the same amount for each. It's as though there's no relationship even though I have the relationships set up. What am I overlooking?
Thank you in advance for your time.
amitchandak , Kudos and thank you for your reply. I tried this version but it still caused the unwanted sum of the entire column regardless of the categories.
In working through this, however, I did realize that the filter I wanted to apply when a slicer value was selected would not flow back up the chain to the dimension table. Therefore, I was able to rectify this by bringing the grouped Categories into the Fact table instead. The resulting code I used simply referenced the category column in the fact table, thus filtering the results.
5 Replies
- Greg_Deckler
Community Champion
Really difficult to tell without some sample data to test with. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Greg_Deckler , Kudos and thank you for the tip! I will definitely streamline my future questions based on this article.
- amitchandak
Super User
Try like
SWITCH( TRUE(), _max = "Category A", CALCULATE( DISTINCTCOUNT( 'Orders'[Order ID] ), 'Payor'[Payor Groups] in{ "X", "Y" } ), _max = "Category B", CALCULATE( DISTINCTCOUNT( 'Orders'[Order ID] ), 'Payor'[Payor Groups] = "Z"), DISTINCTCOUNT( 'Orders'[Order ID]) )amitchandak , Kudos and thank you for your reply. I tried this version but it still caused the unwanted sum of the entire column regardless of the categories.
In working through this, however, I did realize that the filter I wanted to apply when a slicer value was selected would not flow back up the chain to the dimension table. Therefore, I was able to rectify this by bringing the grouped Categories into the Fact table instead. The resulting code I used simply referenced the category column in the fact table, thus filtering the results.
- amitchandak
If you can post the correct fomrula, that will help other users.
