filter by another columns text

[Anonymous]

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,

[edhans]

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

[Anonymous]

Hi @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

[edhans]

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.

[Anonymous]

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

[edhans]

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

[Anonymous]

hi @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

[edhans]

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

[Anonymous]

Hi @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,

[edhans · Accepted 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]
)