Calculated Column to Measure Gives many (unwanted) results

[JimmyBos]

Hello all,

In my PBI file i am changing Calculated Columns to Measures. Am struggling to change the following:

At this moment i have a Calculated Column:

Uitgegeven = IF(POStuklijst[Checked] - POStuklijst[Completed] = 0 , "JA" , "NEE" )

The Measure i created which works (but gives many unwanted results):

Uitgegeven Measure =

var Checked = SELECTEDVALUE(POStuklijst[Checked])

VAR Completed = SELECTEDVALUE(POStuklijst[Completed])

RETURN IF(Checked - Completed = 0, "JA", "NEE")

So the file shows all parts used in a project. The Calculated Column i have shows "JA" or "NEE" for all parts in this project.

The Measure i tried shows many more rows with different parts. These parts are not used in the project and (obviously) show 'zero' for amount.

Does anyone know how to adjust the Measure so i get the same result as the Calculated Column? Thanks in advance!

[AnkitKukreja]

Hi! @JimmyBos 

Please try this: 

Uitgegeven Measure =
IF(
SUMX(
POStuklijst,
IF(POStuklijst[Checked] - POStuklijst[Completed] = 0, 1, 0)
) = COUNTROWS(POStuklijst),
"JA",
"NEE"
)

OR

Uitgegeven Measure =
VAR CheckedSum = SUM(POStuklijst[Checked])
VAR CompletedSum = SUM(POStuklijst[Completed])
RETURN IF(CheckedSum - CompletedSum = 0, "JA", "NEE")

[rajendraongole1 · Accepted solution]

Hi @JimmyBos  - You need to ensure that the measure respects the row context like the calculated column

use the below measure:

Uitgegeven Measure =
VAR Checked = MAX(POStuklijst[Checked])
VAR Completed = MAX(POStuklijst[Completed])
RETURN
IF(Checked - Completed = 0, "JA", "NEE")

If you're seeing extra rows that aren't used in the project, try using a measure that excludes them

Uitgegeven Measure =
VAR Checked = MAX(POStuklijst[Checked])
VAR Completed = MAX(POStuklijst[Completed])
RETURN
IF( NOT(ISBLANK(Checked)) && NOT(ISBLANK(Completed)),
IF(Checked - Completed = 0, "JA", "NEE"),
BLANK()
)

[JimmyBos]

Hello @rajendraongole1 , Thanks again! The second formula (with the ISBLANK) was the solution!

Ofcourse also thanks to @Deku  & @AnkitKukreja for their view on this problem.

[Deku]

Without more context the best I can suggest is

Uitgegeven Measure =

var Checked = sum(POStuklijst[Checked])

VAR Completed = sum(POStuklijst[Completed])

RETURN IF(Checked - Completed = 0, "JA", "NEE")