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
MBBIUser
Regular Visitor

Nested IIF statement in Report Builder linked to SSAS data source

Hi,

 

I am trying to add a calculated member with the following logic - IIF( [Client].[Client Number] NOT IN 028193, 093386, [Client].[Client Type]=62, [Client].[Client Number] IN 028193, 093386). The purpose is to combine certain client numbers and client type which are two distinct fields and report builder doesn't offer "OR" functionality.

 

The logic reads if client not equal to 028193 and 093386 then use client type = 62, otherwise, show those two clients.

 

But I don't know how to do it correctly. If  you have any suggestions, please help. Thank you for reading this!

 

MBBIUser_0-1640293717062.png

 

1 ACCEPTED SOLUTION
MBBIUser
Regular Visitor

I found what I needed in google search. No need to respond to this post anymore. Thanks.

View solution in original post

7 REPLIES 7
MBBIUser
Regular Visitor

I found what I needed in google search. No need to respond to this post anymore. Thanks.

v-yiruan-msft
Community Support
Community Support

Hi @MBBIUser ,

What is the logic of the calculated member and what is the final result you want? The logic is to combine client number and type 62 if the client number is not 028193 and 09338, otherwise, the original client number (028193 and 09338) is displayed? If you have the following table data, what is the final result you want to display?

yingyinr_0-1640588392069.png

In addition, you can refer the following links to get it.

Decision Functions

Writing Complex Expressions

Best Regards

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

I need client number returned. For the example you have here, I will need 028193, 093386, and 093387 with 028134. I need two values (client numbers returned).

 

Would you happen to know if there is "OR" function in there that I could use instead?

 

Thank you!

AlexisOlson
Super User
Super User

Can you give some example rows for what your calculated member should be? I'm not clear even what data type you intend to return (boolean/integer/text).

 

FYI, this is not DAX but expressions written in Microsoft Visual Basic.

I need something like this returned. Client numbers, to be specific. 

 

P.S.: that is good to know it is not DAX and MVB instead. Thanks! 🙂

 

ClientSubsectorClient Type
28193Education62
26203Pharmaceuticals  
216340Marijuana Producers 
83258Nondurable Household Products 
93386Education62
85516Food Products 
212315Medical Supplies 
26389Education62
63511Legal Services 
211931Medical Supplies 
210124Biotechnology 
212809Pharmaceuticals  
208198Medical Equipment & Services / Medical Supplies (20102015) 
213387Chemicals / Chemicals: Diversified (55201000) 
200700Personal Products 
19427Medical Supplies 
13670Medical Equipment 
23685Specialty Chemicals 
211480Chemicals / Chemicals: Diversified (55201000) 
25416Specialty Chemicals 
217553Chemicals / Specialty Chemicals (55201020) 
92012Education 
217522Chemicals / Specialty Chemicals (55201020) 
20871Education62

Hi @MBBIUser 

 

Use this code to add a new column:

Client Type = 
If(Client[Client] in {28193,93386,26389,20871},62,blank())

 

Output:

VahidDM_0-1640699155746.png

 

 

ADD Calculated column: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

I can't use this because I am in the report builder not desktop. But thank you! 🙂

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