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



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! 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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors