Best practice - checking multiple fields for a value

[vanessafvg]

I have about 10 columns that i need to check to see if a value is present then assign it an indicator.  I need to check them all at and if any of them have a value then the indicactor is assigned.

Ways of doing this could be summing them all and if the value is > 0 then assign the indictor.

Just out of curiosity though what do people think is the best way to do this and would you do this in Power Query with a custom column or in DAX?  

[spuder · Accepted solution]

Hi @vanessafvg

I would say it depends.  

My first thought is the same like yours. 

I would use Power Query. It is my first choice for transformation.

A solution in DAX would be by calc column 

Column = IF(ISBLANK(Table1[Column1]+Table1[Column2]+Table1[Column3]);"no values")

But I don't think it is a better way.

[spuder · Accepted solution]

Hi @vanessafvg

I would say it depends.  

My first thought is the same like yours. 

I would use Power Query. It is my first choice for transformation.

A solution in DAX would be by calc column 

Column = IF(ISBLANK(Table1[Column1]+Table1[Column2]+Table1[Column3]);"no values")

But I don't think it is a better way.

[dedelman_clng · Accepted solution]

Do you need the 10 columns for anything else aside from assigning the indicator?  If not, you could possibly unpivot the data, add a calculated column for the indicator, then re-pivot the data with only the needed columns and something like a DISTINCTCOUNT on the indicator column.

I would think you'd be able to do that in the Edit Queries window, so while it technically is PowerQuery, you wouldn't have to write the code yourself.

Hope this helps

David