Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KaiRado
Frequent Visitor

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

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:

KaiRado_0-1664695198964.png

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

KaiRado_2-1664696158267.png

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!

1 ACCEPTED SOLUTION

Hi @KaiRado 
Please refer to attached sample file with the solution

1.png

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

 

 

View solution in original post

8 REPLIES 8
tamerj1
Super User
Super User

Hi @KaiRado 
Try using TREATAS

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

 

 

Hi @tamerj1 !  Unfortunately that didn't work.

 

But thank you for your time!

@KaiRado 

Seems I wrote it the other way around. Please try

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

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

Thank you

@KaiRado 

Sorry I just saw your reply. Please try with REMOVEFILTERS

 

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

 

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!

Hi @KaiRado 
Please refer to attached sample file with the solution

1.png

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

 

 

Hi @tamerj1 , thank you very much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors