Count rows with filter and get a sum on a measure

[Anonymous]

Hi, 

I have a measure named "color" and I want to have a measure that count how many times I have "red" color or another color.

For example I have a table as below : 

Car---------Zone-------- color--------------------Measure

A                 EU                red                                 1

B                 EU                blue                               0

C                 EU               red                                  1

Total                                                                      2

I want a measure which returns 2  

I tried that but I doesn't work...

freq = CALCULATE(COUNTROWS(table),FILTER(table,[color]="red")))

Please if someone can help me,

Thank you 

[NickolajJessen]

[FarhanAhmed]

This should results your desired results. One thing that you can check if you have check "Show Items with No Data" to the CARS field in the table.

Else you can try below expression as well

freq = IF(ISBLANK(CALCULATE(COUNTROWS('Table'),FILTER('Table',[color]="red"))),0,CALCULATE(COUNTROWS('Table'),FILTER('Table',[color]="red")))

[Anonymous]

Hi @FarhanAhmed , thank you for your answer, but I don't get the result that I want : I let you see 

I must obtain 3 as a result and not 9.

[FarhanAhmed]

you want to count Distinct Reds ? the freq measure results are fine though

[Anonymous]

@FarhanAhmed , 

Yes I want to count only red color which hex color is "#FF0000". The freq measure result is false because I should obtain 3 and not 9

[FarhanAhmed]

Try this measure instead

freq = IF(HASONEVALUE('Table'[Car]),

IF(ISBLANK(CALCULATE(COUNTROWS('Table'),FILTER('Table',[color]="red"))),0,CALCULATE(COUNTROWS('Table'),FILTER('Table',[color]="red"))),CALCULATE(DISTINCTCOUNT('Table'[Car]),FILTER('Table','Table'[Color]="Red")))

[Anonymous]

@FarhanAhmed 

I dont obtain the right result, maybe because  [ car ] is not a column but a measure... I obtained the same result as previously. 

With this measure  I have the right filter but I don't know how to have a sum and a total :

freq = COUNTX(Values(table[Zone]), IF ( [Trend_W] = "#FF0000", 1))

You can see the result in the picture that I sent before, 

Thank you so much for your help @FarhanAhmed 

[FarhanAhmed · Accepted solution]

It seems that your "Trend_W" is a measure it self.

Try a maesure like below

freq = COUNTX(Values(table[Zone]), IF ( [Trend_W] = "#FF0000", 1))

_Freq New = SUMX(Values(Table[Car]),Freq)

[Anonymous]

@FarhanAhmed, 

Thank you for the "New_freq" it works !!!