Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vanessafvg
Super User
Super User

Best practice - checking multiple fields for a value

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?  





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




2 ACCEPTED SOLUTIONS
spuder
Resolver IV
Resolver IV

Hi @vanessafvg

 

I would say it depends. Man Very Happy 

 

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.

View solution in original post

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

View solution in original post

2 REPLIES 2
spuder
Resolver IV
Resolver IV

Hi @vanessafvg

 

I would say it depends. Man Very Happy 

 

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.

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors