Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RezaAzimi
Helper I
Helper I

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:

product components.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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?

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

 


Regards
Zubair

Please try my custom visuals

View solution in original post

17 REPLIES 17

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.