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
ThisIsFalse
Resolver I
Resolver I

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

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.