cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThisIsFalse Resolver I
Resolver I

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 column there is a shift indicator. I want to have a visuals display the values for the current shift.  The idea I came up with was to have a calculated column that used a measure to filter the values, but it won't allow this. 

V2PalletsShift = IF([CurrShift] = "Night Shift" && [SHIFT] = "False",[PALLETS],IF([CurrShift] = "Day Shift" && [SHIFT] = "True",[PALLETS],0))

The error it gives says that it will not allow 'placeholder' values in a calculated column for a DQ model. I assume that this is a refence to [CurrShift] the measure that calculates the current shift. I want to use this kind of filter on several visuals in the report which is part of the reason I wanted to put it in the table. Does anyone know of a techique that will work for this? please let me know if you guys have any ideas or need some more info to go on. 
Thanks for your time and effort. 

1 ACCEPTED SOLUTION

Accepted Solutions
ThisIsFalse Resolver I
Resolver I

Re: Dynamic filter on direct query table using Dax

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

View solution in original post

1 REPLY 1
ThisIsFalse Resolver I
Resolver I

Re: Dynamic filter on direct query table using Dax

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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors