How to Count all the DAX Measure Values that have a specific value?

[carlacun]

Dear all,

I have this matrix in PowerBI - this were all taken from a measure calculation.

Now, I want to count all the measure values per color (e.g. how many are green, amber, red) using DAX function too.
Result should be: Green = 5, Amber = 1, and Red = 3

I tried searching online for answers but I can't seem to find the correct expression/functions to use.
Any help and recommendation is highly appreciated. Thank you.

[freginier]

Hey there!

To count occurrences of each specific value (Green, Amber, Red) in your Power BI measure results using DAX, you can use the following approach:

Use the CALCULATE function along with COUNTROWS and FILTER to count the occurrences of each value.

Count_Green =
CALCULATE(
COUNTROWS(YourTable),
YourTable[YourColumn] = "Green"
)

Count_Red =
CALCULATE(
COUNTROWS(YourTable),
YourTable[YourColumn] = "Red"
)

Count_Amber =
CALCULATE(
COUNTROWS(YourTable),
YourTable[YourColumn] = "Amber"
)

Don't forget to replace YourTable with your actual table name and replace YourColumn with the actual column that contains "Green", "Red", and "Amber".

Hope this helps!

😁😁

[carlacun]

Hi there. 

The values I want to count is actually not a column.
It is a result of a 'measure' already. These are all from different measure. 

Its like--
Measure Result 1 =  Green
Measure Result 2 = Red

Measure Result 3 = Green
Measure Result 4 = Amber
and so on.

Now I want to total how many of the results are green, amber, or red.
Any idea on this one?

[freginier]

Hey there!

Since these results are not stored in a single column but are generated by multiple measures, you can create a new DAX measure to count the occurrences of each value.

Create a Measure for Counting "Green"= Count_Green =
VAR MeasuresList =
{
[Measure1],
[Measure2],
[Measure3],
[Measure4],
[Measure5]
}
RETURN
COUNTX( FILTER( MeasuresList, [Value] = "Green" ), [Value] )

For red:

Count_Red =
VAR MeasuresList =
{
[Measure1],
[Measure2],
[Measure3],
[Measure4],
[Measure5]
}
RETURN
COUNTX( FILTER( MeasuresList, [Value] = "Red" ), [Value] )

so on...... 

- The MeasuresList variable stores the values returned by different measures.
- COUNTX is used to iterate over the list and count how many times each value appears.
- FILTER ensures that only occurrences of a specific value (e.g., "Green", "Red", "Amber") are counted.

Hope this helps!

😁😁

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.