CODEWITHSUNDEEP
powerbidax
Ollie
Ollie

Reputation: 337

Power BI - DAX How to filter on an aggregate like COUNT

I'm new to DAX and Power BI. I'm having trouble filtering something that is probably not very complicated but I've finally given up. Any assistance is appreciated.

My starting data looks like this. I want to distinctcount how many drugs each person got (3 for Amy and 2 for Willy), then filter by 3.

Starting Data:

Key         Name      DOB       OTC
AmyZ32874   Amy Z     1/1/1990  Motrin
AmyZ32874   Amy Z     1/1/1990  Tylenol
AmyZ32874   Amy Z     1/1/1990  Bayer
WillyV32906 Willy V   2/2/1990  Motrin
WillyV32906 Willy V   2/2/1990  Tylenol
WillyV32906 Willy V   2/2/1990  Tylenol

Desired Result: Ideally, this would be the desired result because the DisctinctCount of OTC is 3 for Amy Z.

Key         Name      DOB       OTC
AmyZ32874   Amy Z     1/1/1990  Motrin
AmyZ32874   Amy Z     1/1/1990  Tylenol
AmyZ32874   Amy Z     1/1/1990  Bayer

Things I tried:

  1. Creating a distinctcount column on OTC, but this gave me total rows
  2. I tried separating the drugs into separate tables then doing a natural innerjoin, but it threw an error because of the many to many relationship.
  3. I tried doing grouping, but that didn't give me a distinct count.

Thank you!!

Upvotes: 0

Views: 2320

Answers (2)

Ozan Sen
Ozan Sen

Reputation: 2615

If you want a result set as a table in your post, then create a new table and paste this code:

TestTbl = 
VAR SummaryTbl = 
ADDCOLUMNS(
    YourTbl,
    "UniqueCount",CALCULATE(DISTINCTCOUNT(YourTbl[OTC]),ALLEXCEPT(YourTbl,YourTbl[Name]))
    )
RETURN
    FILTER(
        SummaryTbl,[UniqueCount]=3)

Then Result:

Testtttpapopadkdpokoppka

Upvotes: 1

Jos Woolley
Jos Woolley

Reputation: 9052

Create a measure:

MyFilter :=
CALCULATE( DISTINCTCOUNT( Table1[OTC] ), ALLEXCEPT( Table1, Table1[Name] ) )

then drag this into the Filters pane and set it equal to your desired value, e.g. 3.

Upvotes: 1

Related Questions