topic Re: If statement for row and value in row in DAX Commands and Tips

If statement for row and value in row

Irek — Thu, 16 Mar 2023 13:11:29 GMT

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

Re: If statement for row and value in row

WinterMist — Thu, 16 Mar 2023 13:37:16 GMT

Irek

Please try the following.

Regards,

Nathan

Re: If statement for row and value in row

tamerj1 — Thu, 16 Mar 2023 13:50:29 GMT

Hi Irek

if I correctly understand

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

Re: If statement for row and value in row

WinterMist — Thu, 16 Mar 2023 14:02:14 GMT

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

Re: If statement for row and value in row

tamerj1 — Thu, 16 Mar 2023 14:17:56 GMT

WinterMist

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

Re: If statement for row and value in row

WinterMist — Thu, 16 Mar 2023 14:26:06 GMT

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

Re: If statement for row and value in row

Irek — Fri, 17 Mar 2023 11:30:42 GMT

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

Re: If statement for row and value in row

tamerj1 — Fri, 17 Mar 2023 11:43:35 GMT

Irek
How are you merging? Also provide a sample data

Re: If statement for row and value in row

Irek — Fri, 17 Mar 2023 12:38:56 GMT

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

Re: If statement for row and value in row

tamerj1 — Fri, 17 Mar 2023 12:43:05 GMT

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

Re: If statement for row and value in row

Irek — Fri, 17 Mar 2023 13:04:48 GMT

here is really simple sample .