Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear All,
I would like to ask for your help on the below topic.
What I would like to do is to say to power bi to create a measure where on the below table (example) to keep the values of EUR for the country of Poland and for the rest keep local currency.
Amount | Reporting Currency | Country by Business |
166 | Local | Poland |
38 | EUR | Poland |
1000 | Local | Sweden |
100 | EUR | Sweden |
50 | Local | Germany |
50 | EUR | Germany |
Any idea?
In case you need any further explanation please let me know.
Best regards,
Solved! Go to Solution.
I think this will work:
Country Profit =
SUMX(
FILTER(
'Europe Profit',
var CurrencyCode =
IF(
MAX('Europe Profit'[Country by Business]) = "Poland",
"EUR",
"Local"
)
RETURN
'Europe Profit'[Reporting Currency] = CurrencyCode
),
'Europe Profit'[Conv. Rate]
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingNot sure what you mean when you say measure, as that should create a scalar value. If you mean a calculated table, the following DAX will create that by creating a new Table, then pasting this code in.
Table 2 =
FILTER(
'Table',
('Table'[Country by Business] = "Poland" && 'Table'[Reporting Currency] = "EUR")
||
('Table'[Country by Business] <> "Poland" && 'Table'[Reporting Currency] = "Local")
)
If your data is coming from an imported source, Power Query is often a better alternative, but not sure what exactly you are doing, so pick the right tool for the job. To do this in Power Query, it is identical filter logic, just different syntax.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszNL80rUdJRCkotyC8qycxLV3AuLSpKzUuuBAo6gySLKhWSKhWcSosz81KLi5VidaKVDM3MgLI++cmJOUA6ID8nMS8FLGFsAeS7hgahihoaGBggqQ8uT01JzYPJwDUgCZsiK3dPLcpNzKtESECUw4VjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Amount", Int64.Type}, {"Reporting Currency", type text}, {"Country by Business", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each (([Country by Business] = "Poland") and ([Reporting Currency] = "EUR") or ([Country by Business] <> "Poland") and ([Reporting Currency] = "Local")))
in
#"Filtered Rows"
Paste that into a new Blank Query in Power Query via the Advanced Editor. Ignore the first 4 rows. All you really care about to exampine is the #"Filtered Rows" logic. The first few rows are just storing the binary blob of that table and formatting it properly.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans ,
No I dont think I made myself clear on that.
So what I want is to build a table where at the end of the day I will show only one currency the Local one. But in case I have the country of Poland I want it to show the EUR values in stead of the local.
So I want it to do is to say : if I have Poland find the give me the values where it shows EUR.
Let me know if it is clear,
Cheers,
Paris
I thought that is what I did:
If that isn't right, then please mock something up in Excel explaining what you have, and how you want the expected output to look and post screenshots.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans Yes this is what i wanted. Small difference is that I want it without the column of Reporting Currency.
but I dont want to built a new table just to take that. I need a measure that will filter both columns and it will give the proper value.
Reporting Currency Amount =
VAR Country = MAX('Table'[Country by Business])
VAR CurrencyCode =
IF(
Country = "Poland",
"EUR",
"Local"
)
RETURN
CALCULATE(
MAX('Table'[Amount]),
FILTER(
'Table',
'Table'[Reporting Currency] = CurrencyCode
)
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportinghi @edhans
Yes this can work but I have more than 1 lines to sum on the column of the values. in this formula it only returns the Max value of the country.
So the question is, how I should tell it to gather all the values and sum them?
Example.
Conv. Rate | Date | Reporting Currency | Country by Business |
1.66 | 2/1/2020 0:00 | EUR | Poland |
11.82 | 2/1/2020 0:00 | EUR | Poland |
387.83 | 2/1/2020 0:00 | EUR | Poland |
I'm going to ask again. Show your data you have now, in a table format. Then show some screenshots or output of what you expect. This thread is now 7-8 deep and this is the first time you've asked for a SUM. Help me help you. Provide a clear and complete explanation of what you are looking for.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans I am sorry for all the misunderstood and once again thank you for all your support on this! 🙂
Look the below table is the info that I have:
Accounts | Conv. Rate | Reporting Currency | Country by Business |
1. Gross Profit | 7.15 | Local | Poland |
1. Gross Profit | 51.01 | Local | Poland |
1. Gross Profit | 1,672.99 | Local | Poland |
1. Gross Profit | 11.82 | EUR | Poland |
1. Gross Profit | 387.83 | EUR | Poland |
1. Gross Profit | 45.25 | EUR | Poland |
1. Gross Profit | 216.14 | Local | Sweden |
1. Gross Profit | 5.86 | Local | Sweden |
1. Gross Profit | 220.46 | Local | Sweden |
1. Gross Profit | 1,761.57 | Local | Sweden |
1. Gross Profit | 20.29 | EUR | Sweden |
1. Gross Profit | 0.55 | EUR | Sweden |
1. Gross Profit | 20.70 | EUR | Sweden |
1. Gross Profit | 165.41 | EUR | Sweden |
And here is what I want to see.
Country by Business | Conv. Rate |
Poland | 444.9 |
Sweden | 2204.02 |
For Poland is the sum of EUR and for Sweden (and rest of countries that I have) Local reporting currency.
Let me know if you need further info.
Cheers,
I think this will work:
Country Profit =
SUMX(
FILTER(
'Europe Profit',
var CurrencyCode =
IF(
MAX('Europe Profit'[Country by Business]) = "Poland",
"EUR",
"Local"
)
RETURN
'Europe Profit'[Reporting Currency] = CurrencyCode
),
'Europe Profit'[Conv. Rate]
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |