Forum Discussion
Solved
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 r...
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
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.
- Yes that is true :) that will give 3 product numbers.
Here is the MEASURE for X Ones
X Ones = COUNTROWS ( FILTER ( SUMMARIZE ( FILTER ( PumpComponents, PumpComponents[Module] = "X1" || PumpComponents[Module] = "X2" || PumpComponents[Module] = "X3" ), PumpComponents[Product_PN], "Distinct_Count", DISTINCTCOUNT ( PumpComponents[Module] ) ), [Distinct_Count] = 3 ) )