Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic filter on direct query table using Dax

I am trying to filter a table using a field in a table created with the Direct Query model. The table has the date and time in one column the number of products produced in another, and in the last c...
DAX
Direct Query
dynamic filter
  • Anonymous's avatar
    Anonymous
    7 years ago

    So there are two solutions that I came up with that may be more or less useful to others. The first is a bit of a sledge hammer, and that is to use an Azure fucntion to redefine a view of the table being queried by power BI (so that the changes happen on the server side on a schedule). The other is to use a VAR as the placeholder, and this sidesteps most of the objections the PBI engine has to filtering tables dynamicly. Here is an example

    V2 ST/SP Times = 
VAR Shift = [CurrShiftRaw]
VAR TableR =  CALCULATETABLE(GROUPBY('Data Viking 2', 'Data Viking 2'[DateVal], "Start Time", MINX(CURRENTGROUP(),'Data Viking 2'[Eastern Time]),"Stop Time", MAXX(CURRENTGROUP(),'Data Viking 2'[Eastern Time])),'Data Viking 2'[Pallets] > 0, 'Data Viking 2'[DateVal] <> TODAY(), 'Data Viking 2'[Shift] = Shift)
Return TableR
Anonymous's avatar
Anonymous
Not applicable
7 years ago

So there are two solutions that I came up with that may be more or less useful to others. The first is a bit of a sledge hammer, and that is to use an Azure fucntion to redefine a view of the table being queried by power BI (so that the changes happen on the server side on a schedule). The other is to use a VAR as the placeholder, and this sidesteps most of the objections the PBI engine has to filtering tables dynamicly. Here is an example

V2 ST/SP Times = 
VAR Shift = [CurrShiftRaw]
VAR TableR =  CALCULATETABLE(GROUPBY('Data Viking 2', 'Data Viking 2'[DateVal], "Start Time", MINX(CURRENTGROUP(),'Data Viking 2'[Eastern Time]),"Stop Time", MAXX(CURRENTGROUP(),'Data Viking 2'[Eastern Time])),'Data Viking 2'[Pallets] > 0, 'Data Viking 2'[DateVal] <> TODAY(), 'Data Viking 2'[Shift] = Shift)
Return TableR