How to count total red flag and green flag

[chrisyan_manalu]

Hi guys,

I am looking for some support with some DAX that I am struggling right now.  I have built a matrix visual as displayed below.

I have data like this and I want to count how many red flag and green flag in one row with multi period and column.

The result supposed to be there are 2 green colour and there are 9 red colour. 

From the table I have included the following DAX.

DurationFlag =

VAR Result = SUM(FactVehicleCheckList[Duration])

RETURN

IF(ISBLANK(Result),-1,1)

How to make dax or something to make result like that in power bi ?

Can anyone advise me on how to achieve this.

Thanks

[johnt75 · Accepted solution]

Num red flags = COUNTROWS( FILTER(
ADDCOLUMNS( SUMMARIZE( 'Date', 'Date'[Year], 'Date'[Week Number]), "@val", [Duration] ),
[@val] = -1
) )

[chrisyan_manalu]

Hi John

Thank you for your response. 

I tried and it works. 

But I'm still struggling about this case because while I'm uncheck all the filter, the red flag is blank. How to make dax if there is no filter, the red flag has the value which is 11 from the example above.

Thank you.

[v-yiruan-msft]

Hi @chrisyan_manalu ,

What do you mean by "uncheck all the filter"? Could you please provide some sample data for the fields applied to the matrix visual? Are the fields under the Columns option in the screenshot below from the date dimension table? And is [Duration] a measure? If so, could you please provide the formula for it? Also, please provide some special exammples of the logic for calculating the number of red and green flags (with or without filtering). Thank you.

Best Regards