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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kolumam
Post Prodigy
Post Prodigy

Can someone help me to understand the DAX formula ?

Newly Operational in Month = IF(ISBLANK(CALCULATE(SUM(Project[Operating Size]),FILTER(ALLEXCEPT(Project,Countries[Country],'Project Phases'[Phase]),Project[COD Month] = MAX(Dates[Month])))),0,CALCULATE(SUM(Project[Operating Size]),FILTER(ALLEXCEPT(Project,Countries[Country],'Project Phases'[Phase]),Project[COD Month] = MAX(Dates[Month]))))

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @Kolumam,

 

Newly Operational in Month =
IF (
    ISBLANK (
        CALCULATE (
            SUM ( Project[Operating Size] ),
            FILTER (
                ALLEXCEPT ( Project, Countries[Country], 'Project Phases'[Phase] ),
                Project[COD Month] = MAX ( Dates[Month] )
            )
        )
    ),
    0,
    CALCULATE (
        SUM ( Project[Operating Size] ),
        FILTER (
            ALLEXCEPT ( Project, Countries[Country], 'Project Phases'[Phase] ),
            Project[COD Month] = MAX ( Dates[Month] )
        )
    )
)

Based on my assumption, 'Dates' is a calendar table, maybe you added 'Dates'[Month] into a slicer, so, MAX(Dates[Month]) returns a user's selected month in slicer.

 

CALCULATE (
            SUM ( Project[Operating Size] ),
            FILTER (
                ALLEXCEPT ( Project, Countries[Country], 'Project Phases'[Phase] ),
                Project[COD Month] = MAX ( Dates[Month] )
            )
        ) 

 

This CALCULATE expression returns the total value of [Operating Size] grouped by [Country] and [Phase], meanwhile, the COD month should equals the selected month. In other words, it filters the table with the scenario where COD month=selected month, and calculate the total size per country per phase.

 

Newly Operational in Month =
IF (
    ISBLANK (
        <expression>
    ),
    0,
    <expression>
)

This means if <expression> returns blank value, display 0 as result, otherwise, display the result calculated by <expression>.

 

Reference: IF, ISBLANK, ALLEXCEPT, FILTER

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yulgu-msft
Employee
Employee

Hi @Kolumam,

 

Newly Operational in Month =
IF (
    ISBLANK (
        CALCULATE (
            SUM ( Project[Operating Size] ),
            FILTER (
                ALLEXCEPT ( Project, Countries[Country], 'Project Phases'[Phase] ),
                Project[COD Month] = MAX ( Dates[Month] )
            )
        )
    ),
    0,
    CALCULATE (
        SUM ( Project[Operating Size] ),
        FILTER (
            ALLEXCEPT ( Project, Countries[Country], 'Project Phases'[Phase] ),
            Project[COD Month] = MAX ( Dates[Month] )
        )
    )
)

Based on my assumption, 'Dates' is a calendar table, maybe you added 'Dates'[Month] into a slicer, so, MAX(Dates[Month]) returns a user's selected month in slicer.

 

CALCULATE (
            SUM ( Project[Operating Size] ),
            FILTER (
                ALLEXCEPT ( Project, Countries[Country], 'Project Phases'[Phase] ),
                Project[COD Month] = MAX ( Dates[Month] )
            )
        ) 

 

This CALCULATE expression returns the total value of [Operating Size] grouped by [Country] and [Phase], meanwhile, the COD month should equals the selected month. In other words, it filters the table with the scenario where COD month=selected month, and calculate the total size per country per phase.

 

Newly Operational in Month =
IF (
    ISBLANK (
        <expression>
    ),
    0,
    <expression>
)

This means if <expression> returns blank value, display 0 as result, otherwise, display the result calculated by <expression>.

 

Reference: IF, ISBLANK, ALLEXCEPT, FILTER

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.