Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Using IFs in DAX

Hello.

 

I am new to PBI and I am struggling to get some DAX measures right, and I believe my problem is in understanding the IF function. And maybe using Calculate correctly to filter the information.

 

My idea is to get a table like this and add two more columns with the Confirmed Revenue and the Revenue Total, that follow the bellow structure (the highlighted is the database name and after it is the field used):

I am using this FactTable in order to get the information, since I created a Type column to identify from which database the information came from:

 

The Dates FactTable used and the Posted table structure is shown bellow:

(the Posted table has the first day of the month as date, but it is irrelevant since we will use the month to group)

 

Can anyone help me write the two DAX measures for Confirmed Revenue and Revenue Total?
Help is much apreciated 🙂

Thanks in advance!

  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi Anonymous ,

     

    You can try this method:

    Sample data:

    New measures:

    CONFIRMED REVENUE = 
VAR _Staff =
    CALCULATE (
        SUM ( 'Fact Staffing forecast'[Revenue DKK] ),
        FILTER (
            'Fact Staffing forecast',
            'Fact Staffing forecast'[Date].[Month] = SELECTEDVALUE ( 'DimDates'[Month] )
        )
    )
VAR _actual =
    CALCULATE (
        SUM ( 'Fact NAV actual month'[Revenue DKK] ),
        FILTER (
            'Fact NAV actual month',
            'Fact NAV actual month'[Date].[Month] = SELECTEDVALUE ( 'DimDates'[Month] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'DimDates'[Post] ) = "Yes",
        0,
        IF (
            MAX ( 'DimDates'[Month Number] ) = MONTH ( TODAY () ),
            _actual + _Staff,
            _Staff
        )
    )
    REVENUE TOTAL =
VAR _budget =
    CALCULATE (
        SUM ( 'Fact NAV budget'[Revenue DKK] ),
        FILTER (
            'Fact NAV budget',
            'Fact NAV budget'[Date].[Month] = SELECTEDVALUE ( 'DimDates'[Month] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'DimDates'[Post] ) = "Yes", _budget, [CONFIRMED REVENUE] )

    The result is:

    Hope this helps you.

    Here is my PBIX file.

     

    Best Regards,

    Community Support Team _Yinliw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yinliw-msft's avatar
    v-yinliw-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    You can try this method:

    Sample data:

    New measures:

    CONFIRMED REVENUE = 
VAR _Staff =
    CALCULATE (
        SUM ( 'Fact Staffing forecast'[Revenue DKK] ),
        FILTER (
            'Fact Staffing forecast',
            'Fact Staffing forecast'[Date].[Month] = SELECTEDVALUE ( 'DimDates'[Month] )
        )
    )
VAR _actual =
    CALCULATE (
        SUM ( 'Fact NAV actual month'[Revenue DKK] ),
        FILTER (
            'Fact NAV actual month',
            'Fact NAV actual month'[Date].[Month] = SELECTEDVALUE ( 'DimDates'[Month] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'DimDates'[Post] ) = "Yes",
        0,
        IF (
            MAX ( 'DimDates'[Month Number] ) = MONTH ( TODAY () ),
            _actual + _Staff,
            _Staff
        )
    )
    REVENUE TOTAL =
VAR _budget =
    CALCULATE (
        SUM ( 'Fact NAV budget'[Revenue DKK] ),
        FILTER (
            'Fact NAV budget',
            'Fact NAV budget'[Date].[Month] = SELECTEDVALUE ( 'DimDates'[Month] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'DimDates'[Post] ) = "Yes", _budget, [CONFIRMED REVENUE] )

    The result is:

    Hope this helps you.

    Here is my PBIX file.

     

    Best Regards,

    Community Support Team _Yinliw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello.

     

    Thank you so much for the help.

     

    I have been trying to go through the code, but I feel that solution is more complicated than the required since all the tables are connected through the Date in the DimDates table. The sample Data model would be more like this:

     

     

    And we want the ActualPL from the FactNAV Budget, I am so sorry for the error.

     

    Could you please revise these and help me adapt the code above?