Forum Discussion

gmasta1129's avatar
gmasta1129
Icon for Resolver I rankResolver I
1 year ago
Solved

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. 

 

 

 

Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    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

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    1 year ago

    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

  • Akash_Varuna's avatar
    Akash_Varuna
    Icon for Super User rankSuper User
    1 year ago

    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.

  • gmasta1129's avatar
    gmasta1129
    Icon for Resolver I rankResolver I
    1 year ago

    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.