Nested IIF statement in Report Builder linked to SSAS data source

[MBBIUser]

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 · Accepted solution]

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

[v-yiruan-msft]

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?

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

Decision Functions

Writing Complex Expressions

Best Regards

[MBBIUser]

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]

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.

[MBBIUser]

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! 🙂

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

[VahidDM]

Hi @MBBIUser 

Use this code to add a new column:

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

Output:

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/

[MBBIUser]

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