cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Count rows with filter and get a sum on a measure

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

1 ACCEPTED SOLUTION
Community Champion

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)

Proud to be a Super User!

9 REPLIES 9
Solution Sage

Community Champion

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")))``

Proud to be a Super User!

Anonymous
Not applicable

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.

Community Champion

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

Proud to be a Super User!

Anonymous
Not applicable

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

Community Champion

``````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")))``````

Proud to be a Super User!

Anonymous
Not applicable

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

Community Champion

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)

Proud to be a Super User!

Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.