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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Irek
Helper I
Helper I

If statement for row and value in row

Hi everyone, 

 

Need your help again 🙂

 

I have such data:

id cccc2 
1550
101
1500
2 1
411
510

 

 

 

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 cccc2 result
155055
1010
150050
2 11
4111
5101
2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @Irek 

if I correctly understand

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

View solution in original post

WinterMist
Impactful Individual
Impactful Individual

@tamerj1 

 

Ah ok.  This makes sense.  Thanks for explaining.

 

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

 

WinterMist_0-1678976685767.png

 

Thanks for the help.

Nathan

View solution in original post

10 REPLIES 10
Irek
Helper I
Helper I

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

 

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

here is really simple sample . 

 

 

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

 

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

WinterMist
Impactful Individual
Impactful Individual

@tamerj1 

 

Ah ok.  This makes sense.  Thanks for explaining.

 

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

 

WinterMist_0-1678976685767.png

 

Thanks for the help.

Nathan

tamerj1
Super User
Super User

Hi @Irek 

if I correctly understand

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

WinterMist
Impactful Individual
Impactful Individual

@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.

 

WinterMist_0-1678975237343.png

 

Perhaps I am doing something wrong?

 

Regards,

Nathan

@WinterMist 

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

WinterMist
Impactful Individual
Impactful Individual

@Irek 

 

Please try the following.

 

WinterMist_0-1678973662639.png

WinterMist_1-1678973688403.png

 

Regards,

Nathan

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors