Forum Discussion
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!
I found what I needed in google search. No need to respond to this post anymore. Thanks.
7 Replies
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! 🙂
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 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/
I found what I needed in google search. No need to respond to this post anymore. Thanks.
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.
Best Regards
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!
