Count of Measure result

[tonyclifton]

Hello community,

I have a Completion Rate Color measure to use it as conditional color formatting for each process.

Example:

What I want to achieve is that I get the count of each color (e.g. 1x green, 2x yellow, 1x red, 3x grey) so that I can display it in a pie chart visual for example.

Here are the three measures that I created:

CompletedCountMeasure = CALCULATE(COUNT([Job Count]);FILTER('Table';'Table'[Completed] = TRUE()

CompletionRateMeasure =  ROUNDDOWN( DIVIDE([CompletedCountMeasure]; SUM('Table'[Job Count]));2)

CompletionRateColorMeasure = 
var result =  
        SWITCH(TRUE();
           [CompletionRateMeasure] >= 0 &&  [CompletionRateMeasure]   <= 0,70;  "red";
           [CompletionRateMeasure] > 0,70 && [CompletionRateMeasure] <= 0,90; "yellow";
           [CompletionRateMeasure] > 0,90; "green"
         )
return  result

I couldn't find a way to get the count of a measure result. When I try to do it with a calculated column I get a circular dependency error.

Is there a way I can get the count with above setup?

thank you.

[v-xicai]

Hi @tonyclifton  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @tonyclifton ,

You may create measures like DAX below.

Red_Num= CALCULATE(COUNTX('Table', [CompletionRateColorMeasure] ), FILTER('Table', 'Table'[CompletionRateColorMeasure] = "red"))

Yellow_Num= CALCULATE(COUNTX('Table', [CompletionRateColorMeasure] ), FILTER('Table', 'Table'[CompletionRateColorMeasure] = "yellow"))

Green_Num= CALCULATE(COUNTX('Table', [CompletionRateColorMeasure] ), FILTER('Table', 'Table'[CompletionRateColorMeasure] = "green"))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the link from where I can download your PBI file.

[SeanMcLarty · Accepted solution]

@tonyclifton  you could create a measure for each colour.  I've used SUMMARIZE() to group the rows by 'Table'[Process].  There is probably a more elegant way to do it with VALUES().

RedCount = 
VAR vRows = SUMMARIZE(
    'Table',
    'Table'[Process],
    "Color", [CompletionRateColorMeasure]
)

RETURN COUNTX(
    FILTER(
        vRows,
        [Color] = "red"
    ),
    1
)

GreenCount = 
VAR vRows = SUMMARIZE(
    'Table',
    'Table'[Process],
    "Color", [CompletionRateColorMeasure]
)

RETURN COUNTX(
    FILTER(
        vRows,
        [Color] = "green"
    ),
    1
)

YellowCount = 
VAR vRows = SUMMARIZE(
    'Table',
    'Table'[Process],
    "Color", [CompletionRateColorMeasure]
)

RETURN COUNTX(
    FILTER(
        vRows,
        [Color] = "yellow"
    ),
    1
)