Forum Discussion
Countif with multiple criteria
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?
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 )
17 Replies
HI RezaAzimi
May be. Try these
X Ones= CALCULATE ( DISTINCTCOUNT ( Pump_Components[Product_PN] ), Pump_Components[Module] = "X1" || Pump_Components[Module] = "X2" || Pump_Components[Module] = "X3" )Y Ones = CALCULATE ( DISTINCTCOUNT ( Pump_Components[Product_PN] ), Pump_Components[Module] = "Y1" || Pump_Components[Module] = "Y2" || Pump_Components[Module] = "Y3" )For All Modules we can simply use
All(X1 to Y3) = CALCULATE ( DISTINCTCOUNT ( Pump_Components[Product_PN] ) )
okay but my concern is that whether the measure takes into account that the same product number has x1,x2,x3 and y1,y2,y3?
The difficult part is to make the "trace" so it counts for the same product numbers all the way. Therefore, the measure needs to take into account that the product number xxyy has module x1,x2,x3-y1,y2,y3) and not count if another random product number has the module. do you follow my question?
Hi RezaAzimi
So a Product Number should be counted only if it exists in all the Modules X1 to X3?
It really makes life easy when some pastes a sample data set and shows desired results
For example see this post
http://community.powerbi.com/t5/Desktop/Create-a-new-table-with-last-known-record/m-p/266728#M120364
