Forum Discussion
DAX count with count filter
I am needing help with a complex formula needed for error checking. This all needs to be done in DAX (not in the Query editor) as there are dynamic filters in the report that need to impact these counts. Example of dynamic filter is Status toggle for user to select X or not X.
I need to count the number of ID's associated with a sold-to and return a count of sold-to if that first count is greater than 1.
Sold to| ID|Status
1 | A |X
2 | B |
3 | C |
1 | D |
1 | E |
2 | F |X
Returns counts of
1 | 3
2 | 2
3 | 1
Filter for > 1 -- Sold to # 1 has 3 associated ID's which is an error
1 | 3
2 | 2
Next I need to be able to roll all of these up to a single filter (set any count > 1 to "Filter)
8 Replies
Hi bvanevr,
Create a simple measure like this:
ID count = DISTINCTCOUNT(TableName[ID])
Add field [Sold to] and measure [ID count] into table visual, add field [status] into slicer. Click the table visual, apply a visual level filter like below.
Best regards,
Yuliana GuHi bvanevr
Try this
Go to Modelling Tab and press the "NEW TABLE" button
New Table = FILTER ( SUMMARIZE ( TableName, TableName[Sold to], "ID Count", DISTINCTCOUNT ( TableName[ID] ) ), [ID Count] > 1 )Thanks for the fast response
I was able to create the table, but it does not allow for dynamic filtering from within the report. For example if the user does not want status = X In that case the count for Sold-to = 1 would not be 3 but 2 and Sold to = 2 would be 1 and would be filtered out.
Returns counts of
1 | 2
2 | 1
3 | 1
Filter for > 1 -- Sold to # 1 has 2 associated ID's which is an error
1 | 2
Hi, Use this Measure in the Table. Then you can add a slicer for Status
ID Count = CALCULATE ( DISTINCTCOUNT ( TableName[ID] ), FILTER ( VALUES ( TableName[Sold to] ), CALCULATE ( DISTINCTCOUNT ( TableName[ID] ) > 1 ) ) )
