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

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