Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |