CODEWITHSUNDEEP
sqlt-sqlreporting-servicesssasdax
ant
ant

Reputation: 1137

How to joint two tables in dax using custom condition

I have Cartons table, which contains two datatime columns - entering warehouse date and exiting warehouse date. For my report i need to calculate table which shows how many cartons are in the warehouse at the end of the each day. My idea was get number of cartons for each date which have entering date lower than current date and exiting date higher than current date. So i need to translate following sql into dax:

SELECT d.date, COUNT(c.Id) AS 'Count of cartons' FROM #dim d
INNER JOIN Inventory.Cartons c on d.date between c.EnteringWarehouseTime and c.ExitingWarehouseTime
GROUP BY d.date
ORDER By d.date

Where dim is table with all dates.

But all joins in dax can be performed only using relations. I can only make cross join of these tables and filter result, but this operation would take to much time. Do i have another options for this?

Upvotes: 1

Views: 6243

Answers (1)

Tamás Szabolcs
Tamás Szabolcs

Reputation: 101

Actually you can simulate a relationship with dax. However, if I understand correctly your questions and the datamodell, you want to query all cartons that are still in the warehouse at a given time, right? For each day in the Date table you can calculate that how many rows in the Carton table are by filtering it by the currently iterated Day. So this formula calculates: For each day in the date table - VALUES('Date') -, will calculate how many rows in the Cartons table present used some filtering - COUNTROWS('Cartons') -. And the filtering works like this: On the current value of the Day - think as a foreach in C# - it will check that how many rows are in the Cartons table present where it's Exiting date is higher or equal than the current Date's value in the iteration, and Enter date is lower the the current date, or it is BLANK() - so still in the warehouse.

 CALCULATETABLE(
        ADDCOLUMNS(
        VALUES('Date'),
        "Cartons",
        CALCULATE(
             COUNTROWS('Cartons'),
             FILTER(
             'Cartons',
             'Cartons'[EnteringWarehouseTime] <= 'Date'[Date]
             ),
             FILTER(
             'Cartons',
             OR('Cartons'[ExitingWarehouseTime] >= 'Date'[Date],ISBLANK('Cartons'[ExitingWarehouseTime])
             )
        )
        )    
    )

This is very similar to the "Open orders" pattern. Check out daxpatterns.com

If you want to simulate a relationship you can always use the COUNTROWS() > 0 pattern as a filter.

Like if you want to do a SUM(Value) on your main table, but only for those rows that are present in the Referenced table - without relationship:

CALCULATE(
   SUM('MainTable'[Value]),
   FILTER(
   'MainTable',   
    CALCULATE(
       COUNTROWS('ReferencedTable'),
       'ReferencedTable'[PK] = 'MainTable'[FK]
       ) > 0
   )
)

Upvotes: 3

Related Questions