Forum Discussion

RezaAzimi's avatar
RezaAzimi
Icon for Advocate I rankAdvocate I
8 years ago
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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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
)

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

RezaAzimi

 

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

 

RezaAzimi's avatar
RezaAzimi
Icon for Advocate I rankAdvocate I
8 years ago
Yes that is true :) that will give 3 product numbers.
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      RezaAzimi
      Icon for Advocate I rankAdvocate I
      8 years ago

      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?