Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

filter by another columns text

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.

 

AmountReporting CurrencyCountry by Business
166LocalPoland
38EURPoland
1000LocalSweden
100EURSweden
50LocalGermany
50EURGermany

 

Any idea?

 

In case you need any further explanation please let me know.

 

Best regards,

Need Help
  • edhans's avatar
    edhans
    6 years ago

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

9 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        edhans
        Community Champion
        6 years ago

        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.