Forum Discussion
"Time intelligence" for non-time dimension
- 1 year ago
Sure:
MIN = AVERAGE('NBA box score - Player'[TMIN])Selected season = SELECTEDVALUE('NBA season'[SeasonID])'NBA box score - Player' is the table with player stats by game.'NBA season' is the dimension table of the seasons (just a plain list of "Season" / "SeasonID").
- 1 year ago
Hi fpennisi ,
Thank you for the clarification. I encountered a similar issue where the previous month's values didn’t appear when the current month filter was applied in the row fields. I believe the formula below will resolve this issue.
EoPS MIN Previous Season = VAR CurrentSeason = SELECTEDVALUE('NBA season'[Season ID]) VAR PreviousSeason = CurrentSeason - 1 RETURN CALCULATE( [MIN], FILTER( ALL('NBA season'), 'NBA season'[Season ID] = PreviousSeason ) )The reason the previous formula returned blank rows is that when you use a matrix visual with the current season in the row field, Power BI automatically applies a filter to show only the current season. This means that for each row in the visual, only data for the selected season is considered. The filter context created by the visual restricts the data to the specific season in that row.
Your original formula was constrained by this filter context, which applied only to the current season.
Best regards,
Hi fpennisi ,
To evaluate your formula, could you please share the measures [Selected season] and [MIN] with us?
Best regards,
- fpennisi1 year agoRegular Visitor
Sure:
MIN = AVERAGE('NBA box score - Player'[TMIN])Selected season = SELECTEDVALUE('NBA season'[SeasonID])'NBA box score - Player' is the table with player stats by game.'NBA season' is the dimension table of the seasons (just a plain list of "Season" / "SeasonID").
- DataNinja7771 year ago
Super User
Hi fpennisi ,
Thank you for the clarification. I encountered a similar issue where the previous month's values didn’t appear when the current month filter was applied in the row fields. I believe the formula below will resolve this issue.
EoPS MIN Previous Season = VAR CurrentSeason = SELECTEDVALUE('NBA season'[Season ID]) VAR PreviousSeason = CurrentSeason - 1 RETURN CALCULATE( [MIN], FILTER( ALL('NBA season'), 'NBA season'[Season ID] = PreviousSeason ) )The reason the previous formula returned blank rows is that when you use a matrix visual with the current season in the row field, Power BI automatically applies a filter to show only the current season. This means that for each row in the visual, only data for the selected season is considered. The filter context created by the visual restricts the data to the specific season in that row.
Your original formula was constrained by this filter context, which applied only to the current season.
Best regards,
- fpennisi1 year agoRegular Visitor
It worked perfectly!
I guessed it was for the reason you explained and a tried a couple of measures with ALL or REMOVEFILTERS, but I wasn't able to let them work.
Thank you!