Forum Discussion

RezaAzimi's avatar
RezaAzimi
Advocate 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
)

     

RezaAzimi's avatar
RezaAzimi
Advocate I
8 years ago
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's avatar
Zubair_Muhammad
Community 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
    Advocate I
    8 years ago
    Yes that is true :) that will give 3 product numbers.
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community 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
        Advocate 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?