Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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;
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;
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.
I found the error.. your solution worked.. thanks
should probably have been in the Dax section.. how do i move it?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
151 | |
121 | |
73 | |
71 | |
63 |