cancel
Showing results for 
Search instead for 
Did you mean: 
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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!