Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
loisloriot
Helper I
Helper I

DAX Dynamic table / context

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:

loisloriot_0-1701439219265.png

   

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:

loisloriot_12-1701441662120.png

 

 loisloriot_7-1701440065665.png

 

 

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

 loisloriot_10-1701440549199.png

 

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

 

 

 

 

 

 

 

 

 

 

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.

1 ACCEPTED SOLUTION
loisloriot
Helper I
Helper I

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

 

View solution in original post

6 REPLIES 6
sjoerdvn
Resolver V
Resolver V

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
Helper I
Helper I

 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
Resolver V
Resolver V

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
Resolver V
Resolver V

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.


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
Helper I
Helper I

pbix and excel source file:

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors