Forum Discussion

beckyconning's avatar
beckyconning
Helper I
6 years ago
Solved

Filter based on distinctcount

There are a number of results for filtering based on measures however the solutions seem quite specific to the measures in question. As such despite reading considerably over the last day I could not...
Need Help
  • beckyconning's avatar
    beckyconning
    6 years ago

    I solved this issue by clicking "New Table" in the "Modeling" section and providing the following DAX.

     

     

    STANDARD_REPORTS = SUMMARIZE(DRUG_REACTIONS, DRUG_REACTIONS[REPORT_ID],"DRUGS",DISTINCTCOUNT(DRUG_REACTIONS[DRUG]),"REACTIONS",DISTINCTCOUNT(DRUG_REACTIONS[REACTION]))

     

     

    Then right clicking "STANDARD_REPORTS" in the "FIELDS" section and selecting "New column" and providing the following DAX.

     

     

    STANDARD = STANDARD_REPORTS[DRUGS]=1||STANDARD_REPORTS[REACTIONS]=1

     


    Then clicking "Manage Relationships", clicking "New", selecting "STANDARD_REPORTS", and setting "Cross filter direction" to "Both", clicking "OK", then clicking "Close".

    Then using "STANDARD_REPORTS[REPORT_ID]" in my charts, dragging "STANDARD" to "Filters on all pages" and selecting "True".

beckyconning's avatar
beckyconning
Helper I
6 years ago

I solved this issue by clicking "New Table" in the "Modeling" section and providing the following DAX.

 

 

STANDARD_REPORTS = SUMMARIZE(DRUG_REACTIONS, DRUG_REACTIONS[REPORT_ID],"DRUGS",DISTINCTCOUNT(DRUG_REACTIONS[DRUG]),"REACTIONS",DISTINCTCOUNT(DRUG_REACTIONS[REACTION]))

 

 

Then right clicking "STANDARD_REPORTS" in the "FIELDS" section and selecting "New column" and providing the following DAX.

 

 

STANDARD = STANDARD_REPORTS[DRUGS]=1||STANDARD_REPORTS[REACTIONS]=1

 


Then clicking "Manage Relationships", clicking "New", selecting "STANDARD_REPORTS", and setting "Cross filter direction" to "Both", clicking "OK", then clicking "Close".

Then using "STANDARD_REPORTS[REPORT_ID]" in my charts, dragging "STANDARD" to "Filters on all pages" and selecting "True".