topic Re: Using IF statement with measure in DAX Commands and Tips

Using IF statement with measure

RvanMelis — Wed, 20 Apr 2022 19:27:30 GMT

Hello,

I'm facing issues when i try to make an IF statement in a measure, using measures.

I did some research myself but can't find the proper solution for my problem.

I created a table with project information. Using measures to calculate totals within columns. Next i want to create a measure with IF statement, that is using previous measures.

When i try to add the new measure it's showing extremely lots of rows that i don't want to see.

I think it's a common thing but i don't know the answer.

I've created this measure:

OHW op aanneemsom = IF(
 Measure_Facturatie[Mijlpalen gefactureerd] >= Measure_OHW[Aanneemsom],
 0,
   IF(
   Measure_Algemeen[Totaal realisatie] < Measure_OHW[Aanneemsom],
   Measure_Algemeen[Totaal realisatie] - Measure_Facturatie[Mijlpalen gefactureerd],
      IF(
      Measure_Algemeen[Totaal realisatie] >= Measure_OHW[Aanneemsom],
      Measure_OHW[Aanneemsom] - Measure_Facturatie[Mijlpalen gefactureerd],
      0
      )
   )
)

Each measure i use, is also being used in my table. Values are calculated correctly.

And this is the table i created (each line, is a project):

After i add the new measure, this is the result:

I hope you can show my in the right direction.

Greetings,

Rolán

Re: Using IF statement with measure

tamerj1 — Wed, 20 Apr 2022 20:06:20 GMT

Hi RvanMelis

try

OHW op aanneemsom = IF ( NOT ISBLANK ( [Mijlpalen gefactureerd] ) && NOT ISBLANK ( [Aanneemsom] ) && NOT ISBLANK ( [Totaal realisatie] ), IF ( [Mijlpalen gefactureerd] >= [Aanneemsom], 0, IF ( [Totaal realisatie] < [Aanneemsom], [Totaal realisatie] - [Mijlpalen gefactureerd], IF ( [Totaal realisatie] >= [Aanneemsom], [Aanneemsom] - [Mijlpalen gefactureerd], 0 ) ) ) )

Re: Using IF statement with measure

RvanMelis — Thu, 21 Apr 2022 13:35:01 GMT

Hi Tamerj, this works perfectly! Thank you very much.

Can you please explain to me what i did wrong or why your measure works? I'm trying to understand so i can take this into next things.

Re: Using IF statement with measure

tamerj1 — Thu, 21 Apr 2022 13:41:43 GMT

The first if statement: if the two measures are blank they are equal and the if statement forces 0 therefore all the previously hidden rows will appear to disply the value 0

the new code checks first if they are not blank then continues with other conditions otherwise returns blank