Countif with multiple criteria

[RezaAzimi]

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?

[Zubair_Muhammad]

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"
)

[RezaAzimi]

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?

[Zubair_Muhammad]

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

[RezaAzimi]

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.

[Zubair_Muhammad]

@RezaAzimi

In the example below, Do you want the result to be 3...?? (for X1 to X3)

[RezaAzimi]

Yes that is true 🙂 that will give 3 product numbers.

[Zubair_Muhammad]

@RezaAzimi

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
    )
)

[RezaAzimi]

yes I works now but I have a performance issue - I want to see the product numbers in a table format but the measure is really slow. Is it possible to optimize the performance. I guess it is because of the large datasets that I needs to loop through?

[Zubair_Muhammad]

Hi @RezaAzimi

Do you want a table like this?

[RezaAzimi]

Right now I only get the count but I also want to have a detailed table view that highlights the product numbers that fulfill the criteria.

[Zubair_Muhammad]

Hi @RezaAzimi

How would you like the final output to be??

Simple Product Numbers that meet the criteria or something else?

[RezaAzimi]

Based on your measure I got 5848 product numbers - that means I have 5848 product numbers mapped to all three modules (i.e. x1,x2,x3). Now it is interesting for me to know the details about these 5848 product numbers and not only the count itself. So what I want is so to know is the product id for each of the 5848 as well as the component ID. This means my final result will look like this:

product_ID               Component_ID

989879                                   x1

989879                                   x2

989879                                   x3

9989799                                 x1

9989799                                 x2

9989799                                 x3

Put simply, I want to which products did the measure counted. 

[Zubair_Muhammad]

Hi @RezaAzimi

Why not a simple table of 5858 Products that meet these criterion?

Product_ID

989879
9989799 

---

----

Component ID is redundant because we already know they have all 3 X components

[RezaAzimi]

but right now the measure only counts the number so I tried to do a "filter" on the existing dataset but no result is showing up. 

so the simple table you talk about it - needs to come from the existing dataset?

[Zubair_Muhammad · Accepted 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
)

[RezaAzimi]

thank you for the help. it was really helpful:)

[Zubair_Muhammad]

@RezaAzimi

For All Modules we can simply use

All(X1 to Y3) =
CALCULATE ( DISTINCTCOUNT ( Pump_Components[Product_PN] ) )