cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThisIsFalse Frequent Visitor
Frequent Visitor

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
Highlighted
ThisIsFalse Frequent Visitor
Frequent Visitor

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
1 REPLY 1
Highlighted
ThisIsFalse Frequent Visitor
Frequent Visitor

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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 444 members 4,246 guests
Please welcome our newest community members: