Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the following query to summarize a list of numbers and its result
mEarnedCum = CALCULATE ( SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ), FILTER ( ALLSELECTED ( PBIScheduleSummaryPeriod ), PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] ) ) )
The last two periods repeat the 830 value and I was wondering if there was a way to surpress the last one. I want to keep this as a measure so I can have running total detail, while mainintaining the 830 total value
Ideal results would have mEarnedCum = 0 or blank for PeriodEndDate 2/26/2017
Thanks!
Solved! Go to Solution.
Try wrapping an IF looking at ActivePeriodsEarnedMH and if it is blank (ISBLANK) return 0
mEarnedCum = IF ( ISBLANK (PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH]), 0, CALCULATE ( SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ), FILTER ( ALLSELECTED ( PBIScheduleSummaryPeriod ), PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] ) ) ) )
Hope this helps
David
am wondering if adding a simple parameter to your formula would do the trick
SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ),
FILTER (
ALLSELECTED ( PBIScheduleSummaryPeriod ),
PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] && PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] >1
or a Filter parameter: [ActivePeriodsEarnedMH] >1
Try wrapping an IF looking at ActivePeriodsEarnedMH and if it is blank (ISBLANK) return 0
mEarnedCum = IF ( ISBLANK (PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH]), 0, CALCULATE ( SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ), FILTER ( ALLSELECTED ( PBIScheduleSummaryPeriod ), PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] ) ) ) )
Hope this helps
David
You got me there I just had to figure out what to use in the isblank.
I made a measure that simply counts the rows in each period, the massive aggregation of data behind the scenes elimiates the chances for mid project blanks and cleanly addresses the remaining project periods.
mEarnedCum = IF ( ISBLANK ([EarnedMHPeriodCount]), 0, CALCULATE ( SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ), FILTER ( ALLSELECTED ( PBIScheduleSummaryPeriod ), PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] ) ) )
It is a measure, so the, ISBLANK (PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH]),0 is throwing the typical a singe value for column x cannot be determined, since an aggregate function is not specified.
So no luck there, good suggestion for a column though, but I need the inherit behavior of the measure for this to work
This looks similar to an exercise I did in a DAX class. In that case the measure was compared to 0 as the "IF" condition, and if <> 0 the calculation took place, else blank (the else was actually left out of the solution since it defaults to blank)
mEarnedCum = IF ( [ActivePeriodsEarnedMH] <> 0, CALCULATE ( SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ), FILTER ( ALLSELECTED ( PBIScheduleSummaryPeriod ), PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] ) ) ) )
If this doesn't work, can you share an anonymized version of your pbix file?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |