Hi I'm trying to calculate a measure that returns 1 if there is something available in other columns

[lisannegesch]

Hi,

I used to add a column in the PowerQuery Editor: 

= Table.AddColumn(, "Total", each if [lenggt] = 1 then 1 else if [width] = 1 then 1 else if [hight] = 1 then 1 else if [length] = 1 then 1 else 0)

But the dataset is so large that I want to transform it in a measure. But I can't even find the colomns when I'm trying to do so. Should I do this in a calculated column or has anyone suggestions?

Lenght	Width	Hight	Weight	Total
1	0	1	1	1
1	0	1	0	1
1	1	0	1	1
1	1	1	1	1
1	1	0	1	1

[v-tangjie-msft]

Hi @lisannegesch ,

For large amounts of data, measures have a better performance experience than calculated columns.

So we can create  a measure.

Measure = 
var len=MAX('Table'[Lenght])
var wid=MAX('Table'[Width])
var hig=MAX('Table'[Hight])
var wei=MAX('Table'[Weight])
return IF(len = 1 || wid=1 || hig=1 || wei=1,1,0)

Then the result is as follows.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[lisannegesch]

Dear Neeko Tang,

Thanks for your response. This helped al lot. And it does show the expected result. But is it also possible to sum this overtime? In the table I tried to sum all the results but with this measure that's not possible (which I could've forseen but didn't think through). 

[v-tangjie-msft · Accepted solution]

Hi @lisannegesch ,

Overtime is not mentioned in your data, you can mark the answer to this post as a solution and republish the post on how to add up this overtime, thanks in advance.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.