topic Re: Filter out rows in DAX Commands and Tips

Filter out rows

adam_macs — Fri, 12 Aug 2022 11:10:03 GMT

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.

Re: Filter out rows

daXtreme — Fri, 12 Aug 2022 12:26:27 GMT

Use the filter pane for the visual.

Re: Filter out rows

adam_macs — Fri, 12 Aug 2022 13:00:51 GMT

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

Re: Filter out rows

v-jianboli-msft — Thu, 18 Aug 2022 09:27:27 GMT

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.