If statement for row and value in row

[Irek]

Hi everyone, 

Need your help again 🙂

I have such data:

id	cc	cc2
1	55	0
1	0	1
1	50	0
2		1
4	1	1
5	1	0

I need to get column with values from cc and cc2 but with logic that if there is value for Id in CC then take values from CC if not from CC2. Just for rows it is easy for me, but it have to be like if I have even one value for ID in CC then take values from CC. 

these is what I expect to achive 

id	cc	cc2	result
1	55	0	55
1	0	1	0
1	50	0	50
2		1	1
4	1	1	1
5	1	0	1

[tamerj1 · Accepted solution]

Hi @Irek 

if I correctly understand

=
IF (
ISBLANK (
CALCULATE ( MAX ( 'Table'[ cc] ), ALLEXCEPT ( 'Table', 'Table'[id] ) )
),
'Table'[cc2],
'Table'[cc]
)

[Irek]

Thank you @WinterMist and @tamerj1 for help.  Calculations works just fine. 

I'm stacked in different place right now. I can raise another topic but it is on same data so maybe you can help me here. 

The sample table that i provided is a merge from 3 tables. 

Id is from main one. 

cc is from other 

and cc2 from third one. 

I'm getting duplicates after merge them because 

for example 

Id 1 have 11 cc and 2 cc2

I want to use cc (becasue Id 1 have more than one cc) and dont use cc2. 

when I merge the files main first the one with cc Im getting 11 lines for ID 1, when I merge this with third table I;m getting 22. 

Is there a way to merge with condition? same logic like for calculation 

if there is at least one cc take name and value from table cc if not take it from table with cc2. 

Hope it make sense. 

Thank you for your help in advance 

Irek

[tamerj1]

@Irek 
How are you merging? Also provide a sample data

[Irek]

here is really simple sample . 

[Irek]

Im merging queries in Power Query by key column, left join. How can I add a sample file here?

[tamerj1]

@Irek 
Upload to OneDrive, DropBox, WeTransfer or any other and share the link.

[WinterMist · Accepted solution]

@tamerj1 

Ah ok.  This makes sense.  Thanks for explaining.

I misunderstood it as a measure request for a "column" within a table visual.

Thanks for the help.

Nathan

[tamerj1 · Accepted solution]

Hi @Irek 

if I correctly understand

=
IF (
ISBLANK (
CALCULATE ( MAX ( 'Table'[ cc] ), ALLEXCEPT ( 'Table', 'Table'[id] ) )
),
'Table'[cc2],
'Table'[cc]
)

[WinterMist]

@tamerj1 

Just trying to learn from you.

When I attempt your solution, it gives the error:

"A single value for column 'cc' & 'cc2' cannot be determined".

I believe this is because it requires a row context to reference a single row value.

Perhaps I am doing something wrong?

Regards,

Nathan

[tamerj1]

@WinterMist 

This is a calculated column. At least this is what I understood from the question. I could be wrong about it. 

[WinterMist]

@Irek 

Please try the following.

Regards,

Nathan