Can't divide by DISTINCTCOUNTNOBLANK

[msuser48]

When applying the following code to generate "PostCounter", I get the correct output:

Issue: I need to divide not by the static "2" value, but by DISTINCTCOUNTNOBLANK(shocks[ID]), which of course is equal to 2, as such:

Why is DISTINCTCOUNTNOBLANK(Shocks[ID]) not = 2 here, and how do I fix it?

[v-binbinyu-msft · Accepted solution]

Hi @msuser48 ,

This is because of the context. For your need, you need this calculate "DISTINCTCOUNTNOBLANK(Shocks[ID])" always return number 2, but for column visual, when "Years"=1, it return 1,"Years"=2,it return 2, "Years"=4,it return 1.Please refer below screenshot:

You just need adjust your dax formual simply, please try below dax formula:

PostCounter =
VAR _currentDeathCount =
    SELECTEDVALUE ( 'Table'[Years] )
VAR _result =
    CALCULATE (
        SUM ( 'Table'[Shock] ),
        'Table'[Years] <= _currentDeathCount,
        ALLSELECTED ( 'Table' )
    )
VAR _num =
    CALCULATE ( DISTINCTCOUNTNOBLANK ( 'Table'[ID] ), ALL ( 'Table' ) )
RETURN
    IF ( HASONEVALUE ( 'Table'[Years] ), _result, COUNTROWS ( 'Table' ) ) / _num

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.