Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Charles_Tex
Frequent Visitor

DAX - many many relationship calculate without using related

Hi Everyone,

 

Context:

My company has two chains of stores and ships ordres from 3 differents warehouse to customers (nothing unsual so far). Each warehouse has a perimeter of postcode. The difficulty is this perimeter changes depending on the store chain and the date.

 

What i want to do:

I want to countfor each warehouse the number of sales ordres that have been shipped to postcodes inside its perimeter.

 

What i have:

4 tables - with  the data and data model below

tables usedtables used

data model.png

 

 

result i'm expecting:

 

below the intermediate result (to understand what i'm trying to do)

 

sales IDShipped from correct warehouse?should have been shipped from
S001TRUE 
S002TRUE 
S003FALSEW3
S004TRUE 
S005TRUE 
S006TRUE 
S007FALSEW2
S008TRUE 
S009FALSEW3
S010TRUE 

 

 

Final result : 

Warehousenumber of orders shipped in correct perimeter
W15
W20
W33

 

I was thinking to use a CALCULATE and COUNT with a RELATED but I found out that it wasn't possible to use RELATED with a many-many relationship...

i came up with something like this:

 

 

sales orders OK =
CALCULATE (
    COUNTROWS ( Sales ),
    RELATED ( Postcode_Warehouse[Postcode] ) = Sales[destination postcode],
    RELATED ( Postcode_Warehouse[Warehouse attached] ) = Sales[Shipped from],
    RELATED ( Postcode_Warehouse[store] ) = Sales[Store],
    RELATED ( Postcode_Warehouse[1st date in use] ) >= Sales[date of shippement],
    RELATED ( Postcode_Warehouse[last date in use] ) < Sales[date of shippement]
)

 

 

I thought of another solution which was to create a table with postcode unique values to try to by pass that many many problem but it still didn't work out...

 

I hope i made myself clear...

 

Anyway thanks for helping me out on this issue.. i've been on it for quite some time and i'm really hoping someone can help me. 🤞

 

 

 

below the data for the 2 main tables :

sales

sales IDShipped fromdate of shippementdestination postcodestore 
S001W104/03/2020100store 1
S002W105/02/2021100store 1
S003W106/06/2020400store 2
S004W313/12/2020500store 2
S005W306/01/2021400store 2
S006W115/10/2020200store 1
S007W102/03/2021200store 1
S008W124/06/2020200store 1
S009W213/01/2021400store 2
S010W318/07/2020500store 1

 

Postcode_warehouse

PostcodeWarehouse attachedstore1st date in uselast date in use
100W1store 101/01/202031/12/2020
100W1store 201/01/202031/12/2020
200W1store 101/01/202031/12/2020
200W1store 201/01/202031/12/2020
300W2store 101/01/202031/12/2020
300W2store 201/01/202031/12/2020
400W2store 101/01/202031/12/2020
400W3store 201/01/202031/12/2020
500W3store 101/01/202031/12/2020
500W3store 201/01/202031/12/2020
100W1store 101/01/2021 
100W1store 201/01/2021 
200W2store 101/01/2021 
200W1store 201/01/2021 
300W2store 101/01/2021 
300W2store 201/01/2021 
400W2store 101/01/2021 
400W3store 201/01/2021 
500W3store 101/01/2021 
500W3store 201/01/2021 
2 REPLIES 2
amitchandak
Super User
Super User

@Charles_Tex , First of all, is postcode and store is unique combination is Postcode_warehouse ?

if so create a concatenated key

key = [postcode] & " " +[store]

 

In sales a new column

key = [destination postcode] & " " & [store]

 

Can this join be 1-M(sales)

 

do not join store_chian and Postcode_warehouse , i doubt that will be needed

Amitchandak, thanks for your answer.

Unfortunately the combination postcode & store are not unique values in postcode_warehouse, as there is also date columns, therefore the combination can occur on different periods of time...

 

If i follow your logic i shoud do a key = postcode & store & date in postcode_warehouse to have a unique value. Yet i have a start date & end date for this table, so the date isn't relevant it's the period between those two dates.....

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.