The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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)
Proud to be a Super User!
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!
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.
you want to count Distinct Reds ? the freq measure results are fine though
Proud to be a Super User!
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")))
Proud to be a Super User!
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 :
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!
User | Count |
---|---|
80 | |
74 | |
41 | |
30 | |
28 |
User | Count |
---|---|
107 | |
96 | |
53 | |
47 | |
47 |