cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
apollo89 Regular Visitor
Regular Visitor

DAX RELATED() with MANY-TO-MANY RELATIONSHIP

Hi All,

 

Consider the following transaction table:

 

ATTR 1ATTR 2DATE 1DATE 2WEEK 1WEEK 2AMOUNT
AB1/1/20191/8/201920190120190210
AB12/26/20181/8/201920185220190220
AB1/1/20191/15/201920190120190330
AB1/8/20191/15/201920190220190330
CD1/1/20191/8/201920190120190210



DATE1 is a Posting Date and DATE2 is the clearing date of the transaction. WEEK1 and WEEK2 are the fiscal weeks of DATE1 and DATE2 respectively. ATTR are random attributes of the transaction. I need to report the transaction amounts by the 'week of' for the attributes. For example, this SQL Case Statement is what I need to replicate:

 

SUM(CAST(CASE WHEN WEEK 2 > 201902 AND WEEK 1 <= 201902
                            THEN AMOUNT
                   ELSE (
                   CASE WHEN WEEK 1<= 201902 AND DATE 2 IS NULL
                   THEN AMOUNT
                   ELSE 0
                   END)
END AS DECIMAL(17,2))) AS AMT_201902

 

The output:

 

ATTR 1ATTR 2AMT_201852AMT_201901AMT_201902
AB206060
CD0100


In order to avoid creating manual columns for each week, I created a seperate table to create a week range for all the weeks involved. The Range column will be put as columns in my Power Bi Matrix and the Amount in Values.

 

WEEK 1WEEK 2RANGE
201901201902201901
201901201902201902
201852201902201852
201852201902201901
201852201902201902
201901201903201901
201901201903201902
201901201903201903
201902201903201902
201902201903201903

 

The relationship between these two tables becomes many-to-many. How do I use Related or Relatedtable function to calculate to calculate the amounts?

 

I further divided the table above as a bridge table, but I still couldn't figure out how to use RELATED to get the Amounts.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX RELATED() with MANY-TO-MANY RELATIONSHIP

Hi @apollo89

 

You may add key columns and link the two tables as below.Then you may create a measure to get the value.Attached the file for your reference.

1.png

Measure = 
IF (
    SELECTEDVALUE ( Table2[RANGE] ) = 201902,
    SUMX (
        Table1,
        IF (
            Table1[WEEK 2] > 201902
                && Table1[WEEK 1] <= 201902,
            Table1[AMOUNT],
            IF ( Table1[WEEK 1] <= 201902 && Table1[DATE 2] = BLANK (), Table1[AMOUNT], 0 )
        )
    ),
    CALCULATE ( SUM ( Table1[AMOUNT] ) )
)
    + 0

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: DAX RELATED() with MANY-TO-MANY RELATIONSHIP

Hi @apollo89

 

You may add key columns and link the two tables as below.Then you may create a measure to get the value.Attached the file for your reference.

1.png

Measure = 
IF (
    SELECTEDVALUE ( Table2[RANGE] ) = 201902,
    SUMX (
        Table1,
        IF (
            Table1[WEEK 2] > 201902
                && Table1[WEEK 1] <= 201902,
            Table1[AMOUNT],
            IF ( Table1[WEEK 1] <= 201902 && Table1[DATE 2] = BLANK (), Table1[AMOUNT], 0 )
        )
    ),
    CALCULATE ( SUM ( Table1[AMOUNT] ) )
)
    + 0

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
apollo89 Regular Visitor
Regular Visitor

Re: DAX RELATED() with MANY-TO-MANY RELATIONSHIP

This works Cherie! Thanks a lot!

I was under the impression that only RELATED and RELATEDTABLE works across tables.

Thanks again!

apollo89 Regular Visitor
Regular Visitor

Re: DAX RELATED() with MANY-TO-MANY RELATIONSHIP

Hi Cherie,

 

I had a question about how the SELECTEDVALUE function is working.

 

When you say SELECTEDVALUE ( Table2[RANGE] ) = 201902, how is it getting a value of 201902 because as per my understanding of SELECTEDVALUE, it will return a BLANK when it finds mutliple values in the filter contecxt. In this case the IF will always be false.

 

Thanks