topic Re: Dynamic calculation in text statements in Developer

Dynamic calculation in text statements

Anonymous — Thu, 14 May 2020 14:18:13 GMT

HelHi All,

Can you please help me on one more thing I am looking for something like below

a brand sale 98 Percent from Asia

b brand sales 80 percent from Europe

Here each text statement has a calculated value like 98 and 80 which are dynamic when we select filters. This we have achieved in tableau. can we achieve this in Power BI

Please let me know.

Thanks & Regards,

Kumar.

Re: Dynamic calculation in text statements

nandukrishnavs — Thu, 14 May 2020 16:11:24 GMT

@Anonymous

You can use selectedvalue() to get the brand or area. https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

Example

message = VAR _brand = CALCULATE ( SELECTEDVALUE ( 'Table'[brand] ) ) VAR _sales = [Sales] //sales measure VAR _area = CALCULATE ( SELECTEDVALUE ( 'Table'[area] ) ) VAR _message = _brand & " sale " & _sales & " Percent from " & _area RETURN _message

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: Dynamic calculation in text statements

Anonymous — Wed, 20 May 2020 06:57:02 GMT

Hi @Anonymous,

I think you need to do some transform in query editor side to create calculation columns to extract specific value form your text statement, then you can simply calculate on these new columns.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlRIKkrMS1EoTsxJVbC0UAhILUpOzStRUEgrys9VcCzOTFSK1YlWSkJSVqxgYaBQAFUHVuZaWpRfkKqgFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Test = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine(List.ReplaceMatchingItems(Text.Split ([Test], " "),{{"brand", ""},{"Percent", ""},{"percent", "" },{"sale", ","},{"sales", ","},{"from", "," }}))), #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Brand", "Percent", "Region"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Brand", type text}, {"Percent", Int64.Type}, {"Region", type text}}) in #"Changed Type1"

Reference link:

Power Query - Replace multiple substrings in one column

Regards,

Xiaoxin Sheng