cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors