Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]))))
Solved! Go to Solution.
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |