Filter based on distinctcount

[beckyconning]

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 find a sufficient answer for my specific needs.

I have the following measure

STANDARD = IF(DISTINCTCOUNT(ADVERSE_DRUG_REACTIONS[DRUG]) = 1 || DISTINCTCOUNT(ADVERSE_DRUG_REACTIONS[REACTION]) = 1,"STANDARD","NONSTANDARD")

When used in a table with REPORT_ID it gives results such as the following

REPORT_ID,STANDARD
10004519,NONSTANDARD
10005537,NONSTANDARD
10011344,NONSTANDARD
10012542,NONSTANDARD
10012781,STANDARD
10012788,NONSTANDARD
10012853,NONSTANDARD
10013076,STANDARD
10013413,STANDARD

I'd like to filter the results in the other charts and tables on the page to only include those which are STANDARD.

I've also tried doing this by creating a "New Table" as follows.

Table = GROUPBY(ADVERSE_DRUG_REACTIONS,ADVERSE_DRUG_REACTIONS[REPORT_ID],"DRUGS",COUNTAX(CURRENTGROUP(),[DRUG]),"REACTIONS",COUNTAX(CURRENTGROUP(),[REACTION]))

However in the grouping this looses the rest of the information from the table. Perhaps it needs to be joined somehow with the original table?

I assume I'm going about this the wrong way. Any hints or suggestions would be greatly appreaciated.

Thanks!

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[beckyconning]

Thank you @Ashish_Mathur !

Here is the link:

https://drive.google.com/file/d/1ujLF5cHL-TjgJse9tkRTJ50C2bcpQzop/view?usp=sharing

[beckyconning · Accepted solution]

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".