Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
)
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
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.
Hi,
Share the link from where I can download your PBI file.
@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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |