Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Count over Measure with Conditions

Hi guys.   I am trying to get a Count over a measure with specific conditions, like value between 20 and 25% and value over 25%.   I honestly have no clue on how to do it. I built this table to p...
Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Anonymous,

     

    Based on the PBIX file you shared I believe that the  issue is based on context because in the measure you created you are making based on Name and Date but on the count of the percentages you are not giving that same context so the result is not correct.

     

    Create a column based on the date from your table:

     

    MONTH = FORMAT(TABLE[DATE];"mm-yyyy")

    Then create the following measure:

     

    COUNT % 20 - 25 =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALL ( TABLE[NAME]; TABLE[MONTH] );
            TABLE[NAME];
            TABLE[MONTH];
            "% Count"; [MEASURE TO BE COUNTED]
        );
        [MEASURE TO BE COUNTED] >= 0,2
            && [MEASURE TO BE COUNTED] <= 0,25
    )
)

     

     

    Believe this should work, but check the PBIX file I have return to you trough private message.

     

     

    Regards,

    MFelix

Anonymous's avatar
Anonymous
Not applicable
8 years ago

MFelix, the Great:

 

I tried your code and it didn't work, it returns the same value for all the Types. I also tried the alternative on comment below.

 

XXX = 
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE ( ALL ( ''Table1'[Type]; ''Table1'[Type] ); ''Table1'[Type]; "MEASURE TO COUNT"; [MEASURE] );
            [MEASURE TO COUNT] <= 0,25
                && [MEASURE TO COUNT] >= 0,2
        )
    )
)

/*
XXX = 
CALCULATE (
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    ALL ( ''Table1'[Type] );
                    ''Table1'[Type]
                );
                "MEASURE TO COUNT"; [MEASURE]
            );
            [MEASURE TO COUNT] >= 0,2
                && [MEASURE TO COUNT] <= 0,25
        )
    )
)
*/

Any thoughts?

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Anonymous

 

Try this one

 

Measure =
COUNTROWS (
    FILTER (
        VALUES ( Table1[Name] ),
        [MEASURE TO COUNT] < 0.25
            && [MEASURE TO COUNT] > 0.2
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Zubair_Muhammad

     

    Thanks for the suggestion, but no luck still, it returns 1 for every Type.

     

    I also tried like:

     

    XXX =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'Table1'[Type] );
            "MEASURE TO COUNT"; [MEASURE]
        );
        [MEASURE TO COUNT] <= 0,25
            && [MEASURE TO COUNT] >= 0,2
    )
)

    We are missing something here...

    • MFelix's avatar
      MFelix
      Super User
      8 years ago

      Hi Anonymous,

       

      Based on the PBIX file you shared I believe that the  issue is based on context because in the measure you created you are making based on Name and Date but on the count of the percentages you are not giving that same context so the result is not correct.

       

      Create a column based on the date from your table:

       

      MONTH = FORMAT(TABLE[DATE];"mm-yyyy")

      Then create the following measure:

       

      COUNT % 20 - 25 =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALL ( TABLE[NAME]; TABLE[MONTH] );
            TABLE[NAME];
            TABLE[MONTH];
            "% Count"; [MEASURE TO BE COUNTED]
        );
        [MEASURE TO BE COUNTED] >= 0,2
            && [MEASURE TO BE COUNTED] <= 0,25
    )
)

       

       

      Believe this should work, but check the PBIX file I have return to you trough private message.

       

       

      Regards,

      MFelix

    • MFelix's avatar
      MFelix
      Super User
      8 years ago

      Can you share you PBIX with me?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Claro!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      MFelixand Zubair_Muhammad

       

      BTW, the existing measure is something like this:

       

      MEASURE =
VAR X1 =
    CALCULATE ( [SO UNITS]; 'DimP'[COD] = 111 )
        + CALCULATE ( [SO UNITS]; 'DimP'[COD] = 222 )
VAR X2 =
    CALCULATE ( [SO UNITS]; 'DimP'[COD2] = 333 )
        + CALCULATE ( [SO UNITS]; 'DimP'[COD2] = 444 )
RETURN
    DIVIDE ( X1; X2 )