cancel
Showing results for
Did you mean:
Helper I

## Can't divide by DISTINCTCOUNTNOBLANK

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?

1 ACCEPTED SOLUTION
Community Support

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:

``````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.

Community Support

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:

``````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.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors