Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 wh...
DAX
Dynamic
virtual
  • Anonymous's avatar
    Anonymous
    2 years ago

     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's avatar
sjoerdvn
Solution Sage
2 years ago

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.


Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.