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

     

     

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] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      Thank you

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Anonymous 

        Sorry I just saw your reply. Please try with REMOVEFILTERS

         

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