CODEWITHSUNDEEP
powerbidax
Sebastian Hubard
Sebastian Hubard

Reputation: 163

Power BI How to Sum Based on If a Column Contains String from Other Column

I have an Entity column with one row per entity. This table has three columns: Entity ID, a Descriptor, and a Metric. The Descriptor is a concatenation of numerous codes and I would like to see the metrics broken down by code.

I originally just split the Descriptor column into numerous rows but that led to some data relationship issues so I'd like to do it without splitting the Descriptor column.

Desired Result

I tried doing the following DAX formula but it resulted in an error stating "the expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression"

Desired Output Metric = CALCULATE('Metric',CONTAINSSTRING('Entity Table'[Descriptor],'Code Table'[Code]))

Ultimately I'm not even sure I need this as a column, and it may be better as a measure...

Any help would be appreciated. Thank you!

Upvotes: 0

Views: 7120

Answers (1)

ZachMann
ZachMann

Reputation: 51

You can get around "the expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression" by using Filter within your CALCULATE.

Here it is as a created column. I used an IF because 'E' code evaluates to a blank and you wanted a 0.

Desired Output Metric = IF(CALCULATE(SUM('Entity Table'[Metric]),FILTER('Entity Table',CONTAINSSTRING('Entity Table'[Descriptor],'Code Table'[Code])))>0,CALCULATE(SUM('Entity Table'[Metric]),FILTER('Entity Table',CONTAINSSTRING('Entity Table'[Descriptor],'Code Table'[Code]))),0)

Here it is as a measure. Be careful to only use this at the Code detail level. When making a measure you need to use aggregate functions to reference your columns, so I am just doing the MIN(Code) since for any single code the Min() will always evaluate to equal that Code. If you try to use this at a higher summary level you may get some odd answers as it will only total for the MIN() code in the data set you are referencing.

Desired Output Metric = IF(CALCULATE(SUM('Entity Table'[Metric]),FILTER('Entity Table',CONTAINSSTRING('Entity Table'[Descriptor],MIN('Code Table'[Code]))))>0,CALCULATE(SUM('Entity Table'[Metric]),FILTER('Entity Table',CONTAINSSTRING('Entity Table'[Descriptor],MIN('Code Table'[Code])))),0)

Upvotes: 1

Related Questions