Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RvanMelis
Frequent Visitor

Using IF statement with measure

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):

RvanMelis_0-1650482538179.png

 

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

RvanMelis_1-1650482604789.png

 

I hope you can show my in the right direction. 

 

Greetings,

Rolán

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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
            )
        )
    )
)

 

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

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
            )
        )
    )
)

 

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. 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors