Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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

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)







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
NickolajJessen
Solution Sage
Solution Sage

NickolajJessen_0-1655372428062.png

 

FarhanAhmed
Community Champion
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.

 

 

FarhanAhmed_0-1655372271499.png

 

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






Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

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 

Mou_lda63_0-1655373622208.png

 

I must obtain 3 as a result and not 9.

 

 

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







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Anonymous
Not applicable

@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

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






Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Anonymous
Not applicable

@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 
 
 
 
 

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)







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Anonymous
Not applicable

@FarhanAhmed

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors