cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

DAX RELATED() with MANY-TO-MANY RELATIONSHIP

Hi All,

Consider the following transaction table:

 ATTR 1 ATTR 2 DATE 1 DATE 2 WEEK 1 WEEK 2 AMOUNT A B 1/1/2019 1/8/2019 201901 201902 10 A B 12/26/2018 1/8/2019 201852 201902 20 A B 1/1/2019 1/15/2019 201901 201903 30 A B 1/8/2019 1/15/2019 201902 201903 30 C D 1/1/2019 1/8/2019 201901 201902 10

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 1 ATTR 2 AMT_201852 AMT_201901 AMT_201902 A B 20 60 60 C D 0 10 0

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 1 WEEK 2 RANGE 201901 201902 201901 201901 201902 201902 201852 201902 201852 201852 201902 201901 201852 201902 201902 201901 201903 201901 201901 201903 201902 201901 201903 201903 201902 201903 201902 201902 201903 201903

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

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

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.

```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

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

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.

```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.
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!

Regular Visitor

Hi Cherie,