DAX Dynamic table / context

[loisloriot]

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.

[sjoerdvn]

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]))

[loisloriot · Accepted solution]

 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])
)

[sjoerdvn]

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]))

[sjoerdvn]

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.

[loisloriot]

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.

[loisloriot]

pbix and excel source file:

• https://file.io/BnMWDSdfQ5gQ