topic Re: Displaying the same data under another table - using USERELATIONSHUP() maybe? in DAX Commands and Tips

Displaying the same data under another table - using USERELATIONSHUP() maybe?

Anonymous — Sun, 02 Oct 2022 07:36:54 GMT

Hi,

I have a table Sales that is linked to two date tables Date1 and Date2. Let's assume that the data looks like this:

Now I need the measure Sales to display the same data as on the second table while using the date table Date1:

I tried to use USERELATIONSHIP() but it doesn't work. Maybe I was using it wrongly.

Do you have any tips or ideas?

Thank you in advance!

Re: Displaying the same data under another table - using USERELATIONSHUP() maybe?

tamerj1 — Sun, 02 Oct 2022 09:07:01 GMT

Hi Anonymous
Try using TREATAS

Sales = CALCULATE ( SUM ( Sales[Sales] ), TREATAS ( VALUES ( Date1[Date] ), Date2[Date] ) )

Re: Displaying the same data under another table - using USERELATIONSHUP() maybe?

Anonymous — Sun, 02 Oct 2022 14:28:19 GMT

Hi tamerj1 ! Unfortunately that didn't work.

But thank you for your time!

Re: Displaying the same data under another table - using USERELATIONSHUP() maybe?

tamerj1 — Sun, 02 Oct 2022 14:40:18 GMT

Anonymous

Seems I wrote it the other way around. Please try

Sales = CALCULATE ( SUM ( Sales[Sales] ), TREATAS ( VALUES ( Date2[Date] ), Date1[Date] ) )

Re: Displaying the same data under another table - using USERELATIONSHUP() maybe?

Anonymous — Sun, 02 Oct 2022 15:03:59 GMT

Hi tamerj1 , I've tried both ways and I'm getting odd numbers.

Thank you

Re: Displaying the same data under another table - using USERELATIONSHUP() maybe?

tamerj1 — Sun, 02 Oct 2022 16:53:45 GMT

Anonymous

Sorry I just saw your reply. Please try with REMOVEFILTERS

Sales = CALCULATE ( SUM ( Sales[Sales] ), TREATAS ( VALUES ( Date2[Date] ), Date1[Date] ), REMOVEFILTERS ( Date1 ) )

Re: Displaying the same data under another table - using USERELATIONSHUP() maybe?

Anonymous — Sun, 02 Oct 2022 17:52:55 GMT

Hi tamerj1 , unfortunately I'm getting the same result as in the first table with Date1.
I'm not sure if it is even possible to achieve the resut I want.

Thank you so much for your time!

Re: Displaying the same data under another table - using USERELATIONSHUP() maybe?

tamerj1 — Mon, 03 Oct 2022 04:41:46 GMT

Hi Anonymous
Please refer to attached sample file with the solution

Sales Date2 = SUMX ( VALUES ( 'Date 1'[Month] ), CALCULATE ( VAR CurrentMonth = MAX ( 'Date 1'[Month Number] ) VAR Dates = FILTER ( VALUES ( 'Date 2'[Date] ), MONTH ( 'Date 2'[Date] ) = CurrentMonth ) VAR Result = CALCULATE ( [Sales Amount], TREATAS ( Dates, Sales[Order Date] ), REMOVEFILTERS ( 'Date 1' ) ) RETURN Result ) )

Re: Displaying the same data under another table - using USERELATIONSHUP() maybe?

Anonymous — Mon, 03 Oct 2022 16:59:49 GMT

Hi tamerj1 , thank you very much!