Forum Discussion
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 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!
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".
3 Replies
- Ashish_MathurSuper User
Hi,
Share the link from where i can download your PBI file.
- beckyconningHelper I
Thank you Ashish_Mathur !
Here is the link:
https://drive.google.com/file/d/1ujLF5cHL-TjgJse9tkRTJ50C2bcpQzop/view?usp=sharing
- beckyconningHelper I
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".