Forum Discussion
If Statement on a Measure
Hello,
I created a measure that looks at the distinct count of a column with text.
Measure = DISTINCTCOUNT(Table_Name[Investment_fund_name])
The output produced is a number.
I would like to create an if statement on the measure above.
If the output in the measure above is greater than 20, a value of 6 should pull in and if it is less than 20, then a value of 0 should pull in.
If statements and switch are not producting the correct values. I am not sure what I am doing wrong.
Hi gmasta1129,
Thank you for reaching out to Microsoft Fabric Community.
Based on the requirement, please use this below measure it will give either 6 or 0 based on the total number of distinct funds, regardless of the table or visual context where it is used:
Measure = VAR DistinctFundCount = CALCULATE(
DISTINCTCOUNT(Table_Name[Investment_fund_name]),
REMOVEFILTERS(Table_Name)
)
RETURN
IF(DistinctFundCount > 20, 6, 0)
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
3 Replies
Hi gmasta1129,
Thank you for reaching out to Microsoft Fabric Community.
Based on the requirement, please use this below measure it will give either 6 or 0 based on the total number of distinct funds, regardless of the table or visual context where it is used:
Measure = VAR DistinctFundCount = CALCULATE(
DISTINCTCOUNT(Table_Name[Investment_fund_name]),
REMOVEFILTERS(Table_Name)
)
RETURN
IF(DistinctFundCount > 20, 6, 0)
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi gmasta1129
Measure = IF( DISTINCTCOUNT(Table_Name[Investment_fund_name]) > 20, 6, 0 )If this still doesn’t produce the expected result, double-check the calculation context (e.g., filters or relationships) in your report, as they can impact the measure's output.
Hello Akash,
Unfortunately the formula did not work. When I place this measure into the table visual, it creates hundreds of rows rows and each row shows a value of 0.
