Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a requirement to be able to identify rows in a table where there is another row for the same ID with different data. Using an example to explain:
Each row in the table has an "individual ID", a "purchased product ID" and a set of product attributes ("product", "product type", "product sub type", "product term").
I need to be able to flag an 'individual' if they have > 1 "purchased product ID" where:
If I were using SQL I would use a left outer join to the same table, on "individual ID" with relevant WHERE clauses to check the rules and any non-null "individual ID" from the join would be marked as eligible.
How would you go about this?
Is there a way to do this in MQuery?
Is this possible in DAX?
Solved! Go to Solution.
See if this measure works:
Filter INdivd ID =
VAR Details =
CALCULATE (
COUNTROWS (
SUMMARIZE (
'Table',
'Table'[Product Sub-type],
'Table'[Product Term],
'Table'[Product Type]
)
),
ALLEXCEPT ( 'Table', 'Table'[Individ ID], 'Table'[Product ID] )
)
VAR Prods =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Product ID] ),
ALLEXCEPT ( 'Table', 'Table'[Individ ID] )
)
RETURN
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Table'[Individ ID] ),
FILTER ( 'Table', Details > 1 || Prods > 1 )
)
)
I've attached the sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
Thank you Paul, I shall pore over this to understand what it's doing, but looking at your screenshots it looks like a solution. I really appreciate your time and help.
Rich
See if this measure works:
Filter INdivd ID =
VAR Details =
CALCULATE (
COUNTROWS (
SUMMARIZE (
'Table',
'Table'[Product Sub-type],
'Table'[Product Term],
'Table'[Product Type]
)
),
ALLEXCEPT ( 'Table', 'Table'[Individ ID], 'Table'[Product ID] )
)
VAR Prods =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Product ID] ),
ALLEXCEPT ( 'Table', 'Table'[Individ ID] )
)
RETURN
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Table'[Individ ID] ),
FILTER ( 'Table', Details > 1 || Prods > 1 )
)
)
I've attached the sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |