topic Re: DAX Dynamic table / context in DAX Commands and Tips

DAX Dynamic table / context

Anonymous — Fri, 01 Dec 2023 14:51:27 GMT

Hi,

I'm getting stuck on a DAX formula when creating dynamic table based on this requirement:

-Getting all rows where the [AsOfDate] is lesser or equal to the date selected

-Getting all rows where the [Launch Year]-1 is equal to the year of the date selected

-Only getting latest / MAX [AsOfDate]

For example:

I first created the logic in SQL since I'm more conformable than DAX then try to translate it:

CREATE TABLE #TemporaryTable ( LaunchYear INT ,Period INT ,AsOfDate Date ,BCount INT ,ID VARCHAR(4) ); INSERT INTO #TemporaryTable (LaunchYear,Period,AsOfDAte,BCount,ID) VALUES ('2022','1','2/1/2022','12','ASD') ,('2022','2','3/1/2022','5','ASD') ,('2022','3','4/1/2022','3','ASD') ,('2022','4','5/1/2022','2','ASD') ,('2022','5','6/1/2022','123','ASD') ,('2022','6','7/1/2022','14','ASD') ,('2022','7','8/1/2022','16','ASD') ,('2022','8','9/1/2022','1','ASD') ,('2022','1','6/1/2022','9','FGH') ,('2022','2','7/1/2022','3','FGH') ,('2022','3','9/1/2022','4','FGH') ,('2022','1','11/1/2022','12','JKL') ,('2022','2','12/1/2022','5','JKL') ,('2022','3','1/1/2023','3','JKL') ,('2022','4','2/1/2023','2','JKL') ,('2023','1','1/1/2023','0','XXX') DECLARE @DateVar DATE = '2023-01-01'; --'2023-02-01'; --'2023-01-01'; SELECT LaunchYear ,Period ,AsOfDAte ,BCount ,ID FROM ( SELECT * ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID, AsOfDate DESC) RowNb FROM #TemporaryTable WHERE AsOfDate <= @DateVar AND LaunchYear = YEAR(@DateVar) -1 ) A WHERE RowNb = 1 DROP TABLE #TemporaryTable;

Above SQL is behaving as expected and I arrived to this DAX formula:

Dynamic Table = VAR FilterTable = FILTER( 'Sheet1', 'Sheet1'[AsOfDate] <= [DateSelected] && 'Sheet1'[LaunchYear] = YEAR([DateSelected])-1 ) VAR AddRowNumber = ADDCOLUMNS( FilterTable, "RowNb", ROWNUMBER ( FilterTable, ORDERBY ( 'Sheet1'[ID], ASC, Sheet1[AsOfDate], DESC), PARTITIONBY ( 'Sheet1'[ID]) ) ) VAR FilterFirstRow = FILTER(AddRowNumber,[RowNb]=1) VAR Result = SELECTCOLUMNS( FilterFirstRow, "RowNb",[RowNb], "AsOfDate",Sheet1[AsOfDate], "BCount",Sheet1[BCount], "ID",Sheet1[ID], "LaunchYear",Sheet1[LaunchYear], "Period",Sheet1[Period] ) RETURN Result

It's working fine when I evaluate it and 'hardcode' the date that is filtered:

But it's not working when I create it on the report view:

- DateSelected measure seems correct

- DateSelected = SELECTEDVALUE(Sheet1[AsOfDate])

- Tab result is all wrong

The slicer is having a Year-Month column from my Date table that seems to have a proper relationship with the 'Sheet1' table

I guess my issue is somewhere around my DateSelected measure / relationship with the Date table not able to have the correct context but I don't understand why obviously.

Re: DAX Dynamic table / context

Anonymous — Fri, 01 Dec 2023 14:55:08 GMT

pbix and excel source file:

https://file.io/BnMWDSdfQ5gQ

Re: DAX Dynamic table / context

sjoerdvn — Fri, 01 Dec 2023 16:44:56 GMT

There's no such thing as a "dynamic" table in Power BI. There are computed tables, but they are computed on the refresh (load) of the model, so the content will not take into account any slicers or filters in a report.
Having a SQL background myself, I have found that is not always helpfull in understanding Power BI concepts.
So my advice is not to focus on the SQL equivalent but focus on the input data and the modeling of that and the required report output.

Re: DAX Dynamic table / context

Anonymous — Fri, 01 Dec 2023 16:48:23 GMT

Hello, I dynamic table as 'virtual' table or DAX table that will be re-calculated everytime a slicer selection is changing. It's not someting I can do on Power Query / to be refreshed with the dataset.
The DAX creating the table is working when I evaluating it, I think I'm just failing to understand who the context will work in that case.

Re: DAX Dynamic table / context

sjoerdvn — Fri, 01 Dec 2023 17:03:22 GMT

you can try adding two measures like below, and then create a visual with these measures and the ID, LaunchYear and AsOfDate columns (do NOT add BCount and Period as columns!)

Last BCount = CALCULATE(LASTNONBLANKVALUE('Date'[Date],MAX(Sheet1[BCount]), ALL('Date'), 'Date'[Date]<MAX('Date'[Date])) Last Period = CALCULATE(LASTNONBLANKVALUE('Date'[Date],MAX(Sheet1[Period]), ALL('Date'), 'Date'[Date]<MAX('Date'[Date]))

Re: DAX Dynamic table / context

Anonymous — Fri, 01 Dec 2023 21:13:56 GMT

A colleague of mine found working and more elegant solution. No need to create a DAX table, just to get the RANK in a measure, to filter the visuals accordingly (rank = 1) and to remove the relationship with the Data table.

Rank = ROWNUMBER ( FILTER( ALL(Sheet1), [AsOfDate] <= MINX(ALLSELECTED('Date'), [Start of Month]) && [LaunchYear] = MINX(ALLSELECTED('Date'), [Year]) - 1 ), ORDERBY([AsOfDate], DESC), LAST, PARTITIONBY([ID]) )

Re: DAX Dynamic table / context

sjoerdvn — Mon, 04 Dec 2023 14:47:57 GMT

I see that the visual filter on Rank will work, but in general that might not be the best performing solution. I was a bit to quick in posting my (untested) solution, which I think should be:

Last BCount = CALCULATE(LASTNONBLANKVALUE('Sheet1'[AsOfDate],MAX(Sheet1[BCount]), ALL('Date'), 'Date'[Date]<MAX('Date'[Date])) Last Period = CALCULATE(LASTNONBLANKVALUE('Sheet1'[AsOfDate],MAX(Sheet1[Period]), ALL('Date'), 'Date'[Date]<MAX('Date'[Date]))