Help with FIFO logic with two Tables in DirectQuery
Hello, I'm struggling with creating a way to measure and/or visualize if a Load Unit that has been delivered to us, was also unloaded first. Deliveries have Priority 1-3 so what we want is, that the first delivery arrived is also the one that is unloaded first. I'm stuck with working in DQ and have to tables to do this- Transports Table and Unloading Table Transports Table has the Columns LoadUnitId, Priority and ActualArrival Timestamp. Unloading Table has the column StarTime Unloading. Something like this: LoadUnitId Priority ActualArrival StartTime Unloading 1234 1 01.01.2025 08:00 01.01.2025 08:15 4321 2 01.01.2025 07:35 01.01.2025 07:56 2134 1 01.01.2025 07:15 01.01.2025 08:19 What kind of expression can be used to check if a Load Unit that has the same priority, was delivered before another one, but unloaded later. I've been thinking of using Ranks but DirectQuery is a bit tricky and won't allow that. Maybe Flags would do the trick but I'm unsure how to use them in this case exactly. Any help would be highly appreciated. Thank you very much 🙂
Directquery limitation summarize
Hi, Context: I have a table with 7 billion rows in bigquery, the table is partitioned and clustered and performs well when sending queries. I'm trying to get the number of unique users based on a certain condition and the following SQL syntax works perfectly. SELECT COUNT(DISTINCT users) AS cnt_users FROM ( SELECT users, AVG(transactions) AS transactions FROM `project.dataset.canales` WHERE Date BETWEEN '2023-10-01' AND '2023-11-05' GROUP BY users) WHERE transactions BETWEEN 15 AND 25; The result is one row with the value and the query runs in 2-3 seconds. When translate this query to dax: light_users = VAR _users = SUMMARIZECOLUMNS ( canales[users], "avg_trx", AVERAGE ( canales[transactions] ) ) VAR _filter = FILTER ( _users, [avg_trx] >= 15 && [avg_trx] <= 25 ) RETURN COUNTROWS ( _filter ) The result in power bi is Looking at the query created by Dax in bigquery I can see that Dax created a temp table but the quantity is 1+ million records The table returns to Power BI to make the final calculation. So, How can I calculate all directly in the source and solve the problem with the returned rows? I tried using vars, without vars and nothing works for me. Thanks
How do I select which tables to calculate on based on the date?
I have 4 tables, say, data_2020, data_2021, data_2022, and calendar, which are all connected through DirectQuery (because each dataset is too large), except for the calendar table, which is just a lookup table. Now, for example, I want to calculate the total profits. So, I have a DAX measure that is defined as Total Profit = SUM('data_2022'[total_profit]) + SUM('data_2021'[total_profit]) + SUM('data_2020'[total_profit]) But I was told that this would still genearate queries for the data_2021 and data_2020 tables even if 'calendar'[date] is filtered to show only data for 2022. I have (I think) a solution that is more optimized and it is defined as Total Profit = VAR last_date_2021 = CALCULATE(LASTDATE('data_2021'[date]), ALL('data_2021'[date])) VAR last_date_2020 = CALCULATE(LASTDATE('data_2020'[date]), ALL('data_2020'[date])) RETURN SUM('data_2022'[total_profit]) + IF(MIN('calendar'[date]) < last_date_2021, SUM('data_2021'[total_profit])) + IF(MIN('calendar'[date]) < last_date_2020, SUM('data_2020'[total_profit])) But this only works best if I filter the minimum date in 'calendar'[date]; I don't think it would optimize the cases wherein, for example, I set the max date to be 2021 or earlier. How do I improve on this? Any help would be much appreaciated. Thank you in advance.
function CALCULATE in Direct Query?
Hi Guys. Currently I use this code to find the last position of a counter for a team within the same column, which is fed by multiple teams. The model was in import mode, which presented several connection problems with other DirectQuery tables, I want to convert the table to DirectQuery but it tells me that I cannot use CALCULATE for DirectQuery, how can I find this same result without having formula restriction in DirectQuery
