Forum Discussion

Jugu22's avatar
Jugu22
Regular Visitor
8 years ago
Solved

Create relation in 2 tables

Hello,   I want to create a relation between 2 tables to create a ratio : on the numerator an amount from a table and on the denominator an amount on the other table.   The problem is I have a lo...
Data Modeling
  • v-haibl-msft's avatar
    v-haibl-msft
    8 years ago

    Jugu22

     

    You can create a measure with following DAX formula without creating relationship between these two tables.

     

    Measure = 
VAR TempDate =
    CALCULATE ( MAX ( Table1[Date] ) )
VAR TempExpectedSales =
    CALCULATE (
        SUM ( Table2[Expected Sales] ),
        FILTER ( ALL ( Table2 ), Table2[Date] = TempDate )
    )
RETURN
    CALCULATE ( SUM ( Table1[Actual Sales] ) ) / TempExpectedSales

     

    Best Regards,
    Herbert

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Jugu22

 

You can create a measure with following DAX formula without creating relationship between these two tables.

 

Measure = 
VAR TempDate =
    CALCULATE ( MAX ( Table1[Date] ) )
VAR TempExpectedSales =
    CALCULATE (
        SUM ( Table2[Expected Sales] ),
        FILTER ( ALL ( Table2 ), Table2[Date] = TempDate )
    )
RETURN
    CALCULATE ( SUM ( Table1[Actual Sales] ) ) / TempExpectedSales

 

Best Regards,
Herbert

Jugu22's avatar
Jugu22
Regular Visitor
8 years ago

It works on my file, thanks a lot.

 

I have a last question, here is my formula :

 RETURN
   (CALCULATE(CALCULATE(SUM('Table1'[Actual Sales]);OR('Table1[Filter]=1;'Table1'[Filter1]=2));'Table1'[Filter2]="00164"))/TempExpectedSales

 

But I want Filter2 (in red) not to be fixed. Can I use a code to change this filter from the rapport? It would be perfect if I could use a segment to change the filter of the measure directly to make the changes dynamic.  

I don't know if I'm clear, again thanks a lot for your solution.