cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
e_cgop
Regular Visitor

Text replace with Blank on IF Loop

Hi Team, 

Thanks in advance for your efforts to help me. 

I need a small help on IF loop on PowerBi

 

 

Location = IF(ISERROR(LOOKUPVALUE(Location[Address],Location[Location Name],'Opened Ticket'[Reported By.Location - Room])),
IF(ISERROR(LOOKUPVALUE(Location[Address],Location[Location Name],'Opened Ticket'[Reported By.Organization Unit])),
"OTHERS",
LOOKUPVALUE(Location[Address],Location[Location Name],'Opened Ticket'[Reported By.Location - Room])),
LOOKUPVALUE(Location[Address],Location[Location Name],'Opened Ticket'[Reported By.Organization Unit])
)

 


Above is my code, I'm getting the results correctly but, those which can find any results should return "OTHERS". For some reason, it just comes as BLANK.  

Do you think you can help?Picture 4 - Location.PNGPicture 3 - Opened ticket.PNGPicture 2 - Opened ticket.PNGPicture 1 - Opened ticket.PNG

 

@selimovd 

 

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @e_cgop ,

 

it's because of the order of you IFs. It won't return "OTHERS". In general I find that approach a little hard to read.

I also don't think you need ISERROR as LOOKUPVALUE just returns BLANK() if it won't find a match.

 

So I would use variables and a combination of SWITCH and TRUE instead of the IFs. For me that is more clear to read. Check it out:

Location =
VAR vReportedByLocationRoom =
    LOOKUPVALUE(
        Location[Address],
        Location[Location Name], 'Opened Ticket'[Reported By.Location - Room]
    )
VAR vReportedByOrganizationUnit =
    LOOKUPVALUE(
        Location[Address],
        Location[Location Name], 'Opened Ticket'[Reported By.Organization Unit]
    )
RETURN
    SWITCH(
        TRUE(),
        vReportedByLocationRoom <> BLANK(), vReportedByLocationRoom,
        vReportedByOrganizationUnit <> BLANK(), vReportedByOrganizationUnit,
        "OTHERS"
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

3 REPLIES 3
selimovd
Super User
Super User

Hey @e_cgop ,

 

it's because of the order of you IFs. It won't return "OTHERS". In general I find that approach a little hard to read.

I also don't think you need ISERROR as LOOKUPVALUE just returns BLANK() if it won't find a match.

 

So I would use variables and a combination of SWITCH and TRUE instead of the IFs. For me that is more clear to read. Check it out:

Location =
VAR vReportedByLocationRoom =
    LOOKUPVALUE(
        Location[Address],
        Location[Location Name], 'Opened Ticket'[Reported By.Location - Room]
    )
VAR vReportedByOrganizationUnit =
    LOOKUPVALUE(
        Location[Address],
        Location[Location Name], 'Opened Ticket'[Reported By.Organization Unit]
    )
RETURN
    SWITCH(
        TRUE(),
        vReportedByLocationRoom <> BLANK(), vReportedByLocationRoom,
        vReportedByOrganizationUnit <> BLANK(), vReportedByOrganizationUnit,
        "OTHERS"
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

Thank you very much 😃
This indeed worked like charm.
I'll be very happy to give you more than 1 thumbs up 😉

Have a wonderful day 

Hey @e_cgop ,

 

I'm happy it works 😊

Best regards
Denis

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors