Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Im trying to achieve a COUNTIF of the unique values within a specific column to be filter later in the dashboard.
I can use the formula comfortably in MS Excel (see image below)
But trying to replicate this in power bi I am finding difficult. This is what I had done so far:
COUNTIF for Duplicate ID =
VAR RowID = fact_food_license[pref]
RETURN
COUNTROWS(
FILTER(
ALL(fact_food_license),
RowID = fact_food_license[pref]
)
)
The error message I am receiving is:
A single value for column 'ID' in table 'fact_food_license' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result..
Any assitance would be grateful!
Solved! Go to Solution.
@Anonymous
pls try
calculate(countrows(fact_food_license),allexcept(fact_food_license,fact_food_license[pref]))
Proud to be a Super User!
Hi @Anonymous
You can update your measure code with below code:-
COUNTIF for Duplicate ID =
COUNTROWS(
FILTER(
ALL(fact_food_license),
fact_food_license[pref] = MAX(fact_food_license[pref])
)
)
Add your pref column along with above measure on your table visual.
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Thanks for you response Samarth
Your solution is only returning the value: 1.
The anticipating results should be 1, 2 or 3
@Anonymous
pls try
calculate(countrows(fact_food_license),allexcept(fact_food_license,fact_food_license[pref]))
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |