Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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:

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!

DAX
USERELATIONSHIP
  • tamerj1's avatar
    tamerj1
    3 years ago

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

     

     

8 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 
    Try using TREATAS

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi tamerj1 !  Unfortunately that didn't work.

       

      But thank you for your time!

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Anonymous 

        Seems I wrote it the other way around. Please try

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