Forum Discussion
Filter out rows
Hi, I’m trying to figure out how to filter our Trusts with a "Count of Mat Customers" = Blank but show the entire Trust and its Establishments if it has at least 1 "MAT Customer".
So, from the example, i want to show Zenith Multi Academy Trust and all the Establishments within that Trust because it has one MAT customer in it. But i would like to filter out "1Excellence Multi Academy Trust" and "5 Dimensions Trust" as they have no MAT customers.
So essentially, if Trust contains >=1 Mat customer show entire trust and all its establishments else filter it out.
Unfortunately, i have no idea what DAX command or how to go about this. Im thinking some kind of IF statement maybe? Any assistance much appreciated.
Hi adam_macs ,
Based on your description, I have created a simple sample:
Then create a measure to help filter the data:
Flag = IF(ISBLANK( CALCULATE(COUNT('Table'[MAT Coustomers]),FILTER(ALL('Table'),[Trust]=MAX('Table'[Trust])))),0,1)Apply it to the visual:
Then filter the data:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Use the filter pane for the visual.
Unfortunately that wont work either as it removes the other establishments within the trust
Hi adam_macs ,
Based on your description, I have created a simple sample:
Then create a measure to help filter the data:
Flag = IF(ISBLANK( CALCULATE(COUNT('Table'[MAT Coustomers]),FILTER(ALL('Table'),[Trust]=MAX('Table'[Trust])))),0,1)Apply it to the visual:
Then filter the data:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
