Distinct count with filter for measure

[Anonymous]

Hi,

Applications measure counts distinct Transaction ID in the Fact table and AppAverageSchool calculates an average of distinct rows for a school.

Applications = 
    CALCULATE (  DISTINCTCOUNT(Transactions[Transaction ID])   )

AppAverageSchool = 
VAR ProgPerSch =
    CALCULATE (     DISTINCTCOUNT ( 'Dim Programs'[Program Code] ),
        ALLEXCEPT ( 'Dim Programs','Dim Programs'[School Name] )    )

VAR AppPerSch =
    CALCULATE ( [Applications], ALLEXCEPT ('Dim Programs','Dim Programs'[School Name] ) )
RETURN
    DIVIDE (AppPerSch, ProgPerSch, 99999 )

I want to filter out a program from the distinct count in var ProgramPerSchool when it is not present in the Fact table. Secondly, also want to remove a program when it has no application from AppAverageSchool measure calculation, as highlighted in yellow colour.

Link for PBIX file and Excel File here

Thanks for the great support

[dax · Accepted solution]

Hi DataPro, 

According to your description, it seems  that you wnat to remove highlight records, right? If I have something misundertsood, please correct me.

If so, you could try  to apply filter on measure Applications like below

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dax · Accepted solution]

Hi DataPro, 

According to your description, it seems  that you wnat to remove highlight records, right? If I have something misundertsood, please correct me.

If so, you could try  to apply filter on measure Applications like below

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @dax , yes Filters is one of the options but I am looking for a Dax query that can handle it.

Thanks,