Best way to filter records by a separate grouping than is used in charts and tables

[beckyconning]

In SQL (this sample is Snowflake dialect but it could be easily modified for T-SQL) I would do this (although perhaps there is a better way).

select * from adverse_drug_reactions as reports right join (select count(distinct drug) = 1 or count(distinct reaction) = 1 as valid, report_id from adverse_drug_reactions group by report_id having not valid) as valid_reports on reports.report_id = valid_reports.report_id;

I could do the equivilant using the Power Query Editor and "Merge Queries" however I'm wondering if there is a preferable way of doing this in Power BI.

[v-shex-msft · Accepted solution]

HI @beckyconning ,

I'd like to suggest you expand 'advanced option' on SQL connector and copy your code to t-sql statement to get a merged table instead of getting multiple tables and merge them on power query side.

Regards,

Xiaoxin Sheng

[beckyconning]

Essentially I want to filter out records assoicated with more than one reaction or more than one drug.

Records assoicated with reports that contain more than one reaction but only a single drug should be included.

Records associated with reports that contain more than one drug but only a single reaction should be included.
Records with a single drug and a single reaction should be included.

Other records should be filtered away.

Each row is a potential reaction to a drug e.g.

REPORT_ID,DRUG,REACTION,DRUG_CHARACTERIZATION_CODE,DRUG_CLASS,DRUG_INDICATION
10004519,OXYCONTIN (NDA 22-272),Osteopenia,1,NULL,OSTEOARTHRITIS
10004519,OXYCONTIN (NDA 22-272),Osteoporosis,1,NULL,OSTEOARTHRITIS