Forum Discussion
Countif with multiple criteria
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 )
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"
)
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
- Yes it should only count the product number when there is a match for all x modules as well y modules. I know and you are right it would be much easier with an example but I will remember it in the future.