topic Re: "Time intelligence" for non-time dimension in DAX Commands and Tips

"Time intelligence" for non-time dimension

fpennisi — Sat, 12 Oct 2024 11:35:29 GMT

Hi,

I'm working with a NBA Dataset.

I have a table showing the season, where a Season as an ID which is the starting year of the season (e.g. for season 2023/24 the ID is 2023).

I would like to show, for a given season, player averages related to the previous one.

I created the measure highlited below, that takes the average minutes overall (it's another measure) and calculates for the previous season of the selected one:

But the measure is not shown and I think that the reason is that the filter context of each row has the season set to the selected one, while the measure takes the values of another season.

How can I correct the measure to get the desired result?

Re: "Time intelligence" for non-time dimension

DataNinja777 — Sat, 12 Oct 2024 14:26:56 GMT

Hi fpennisi ,

To evaluate your formula, could you please share the measures [Selected season] and [MIN] with us?

Best regards,

Re: "Time intelligence" for non-time dimension

fpennisi — Sat, 12 Oct 2024 16:08:37 GMT

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").

Re: "Time intelligence" for non-time dimension

DataNinja777 — Sun, 13 Oct 2024 04:06:01 GMT

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,

Re: "Time intelligence" for non-time dimension

fpennisi — Sun, 13 Oct 2024 09:00:44 GMT

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!