Filter out rows

[adam_macs]

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.

[v-jianboli-msft · Accepted solution]

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.

[daXtreme]

Use the filter pane for the visual.

[adam_macs]

Unfortunately that wont work either as it removes the other establishments within the trust