Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have tried to search for a solution for this issue but I could not find any suitable although it requires a countif function to a large extent. But anyways, I have a table that shows the relationship between six generic components data for each product number. For simplicity reason let's name the generic components for - x1,x2,x3 and y1,y2,y3. A snip of the data looks like this:
So I am interested to know how many unique product number do I have in my table that has been been mapped to 3 of the components - x1, x2, x3, how many to only y1,y2,y3 components and how many of them to all six (i.e. x1,x2,x3 and y1,y2,y3 ).
So in fact it is a count with multiple criteria but I could not make it work with this DAX:
Count of Product_PN with impeller, stator and rotor w/shaft components pn =
CALCULATE(
DISTINCTCOUNT('Pump components'[Product_PN]);
FILTER(VALUES('Pump components'[Component_PN]);CALCULATE(COUNTROWS('Pump components');'Pump components'[Module]="x1" || 'Pump components'[Module]="x2" || 'Pump components'[Module]="x3" || 'Pump components'[Module]="y1" || 'Pump components'[Module]="y2" || 'Pump components'[Module]="y3")))
Can someone assist me on this?
Solved! Go to Solution.
HI @RezaAzimi
Go to Modelling Tab and press the NEW TABLE button and enter this formula
You will get a list of all 5854 products that meet these criterion
NEW TABLE = FILTER ( SUMMARIZE ( FILTER ( PumpComponents, PumpComponents[Module] = "X1" || PumpComponents[Module] = "X2" || PumpComponents[Module] = "X3" ), PumpComponents[Product_PN], "Distinct_Count", DISTINCTCOUNT ( PumpComponents[Module] ) ), [Distinct_Count] = 3 )
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
108 | |
96 | |
95 | |
74 | |
71 |