Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter on transactional level

Faily new to DAX.  So maybe my approach isn't to most straight way forward. If so please feel free to suggest another approach.

I'm trying to build a consolidation tool.  To consolidate P/L based on an input exch. rate from Excel(not the exchange rate table in the database I have a reason for this).  I have build this on Microsoft Cronus data.   Which contains 2 companies, on which books in USD  another in GBP.   So i have build and input table in excel.

Where we only specify  Currency code,  year, month & Exc. Rate.

 

So what i'm trying to achieve now, is the user can choose yeah and month no in a Slider.  From which i can they get the correct set of exchange rates.  So far it works with this formula 

 

Group_Rate = CALCULATE(Values(Cons_Rates[Exc. Rate]);FILTER(Cons_Rates;Cons_Rates[Year] = MAX(D_Time[Year]));FILTER(Cons_Rates;Cons_Rates[Month]=MAX(D_Time[MonthNo])))
 
The problem comes when I want it to interact with the finance table, where i have put on the Company Currency Code on each transaction.   Since  Group_Rate in the case contains 2 rates.   But it needs to Choose on the one which applied for each transaction.  I know i need to end up with a Sumx to combine the finance amount with the exhange rate.  but for beginners. i just wanted to check it came with the correct values.  but it's doesn't really.
 
As you can see it comes with 1,20 for both companies..     so how do i get it so "relate"  without  creating a relation between the tables?    I have tried  adding a third filter    FILTER(Cons_Rate; Cons_Rate[Company_LCY_CODE] = F_FinanceTransactions[Company_LCY_Code])   but that doesn't help.

When this is fixed it needs to be used in an Sumx which sums finance amount.  but it needs to have some time intelligence.  If the D_Accountledger[Incomebalancetype] = 1  it  need to Sum fra "start of time" to end date on the selected slicers.   And if it's 0, follow the filters.  But BOTH needs the same exchange rate from above.  So the solution need to support being used in the way.
 
In advance, Thank you very much for your time
Show and Tell
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    You should not create a relationship between the tables as you said. We can create a measure like this based on two slicers.

     

    Group_Rate =
VAR a =
    MAX ( D_Time[Year] )
VAR b =
    MAX ( D_Time[MonthNo] )
RETURN
    CALCULATE (
        MAX ( Cons_Rates[Exc. Rate] );
        FILTER ( Cons_Rates; Cons_Rates[Year] = a && Cons_Rates[Month] = b )
    )

     

     
    As you can see it comes with 1,20 for both companies..     so how do i get it so "relate"  without  creating a relation between the tables?    I have tried  adding a third filter    FILTER(Cons_Rate; 

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    should probably have been in the Dax section.. how do i move it?

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    You should not create a relationship between the tables as you said. We can create a measure like this based on two slicers.

     

    Group_Rate =
VAR a =
    MAX ( D_Time[Year] )
VAR b =
    MAX ( D_Time[MonthNo] )
RETURN
    CALCULATE (
        MAX ( Cons_Rates[Exc. Rate] );
        FILTER ( Cons_Rates; Cons_Rates[Year] = a && Cons_Rates[Month] = b )
    )

     

     
    As you can see it comes with 1,20 for both companies..     so how do i get it so "relate"  without  creating a relation between the tables?    I have tried  adding a third filter    FILTER(Cons_Rate; 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the effort. 

       

      Unfortunatly it's still returning the same result for both companies.  Like in the example show in OP.

      It seems to be missing the final filter link..    Something like    Filter(Cons_rates; Cons_Rates[Company] = F_FinanceTransactions[Company] )      So it will return 1.2 for the UK company,  but 1 for the US company.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I found the error..  your solution worked.. thanks