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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

How to use switch function properly

Hello,

 

I would like to choose among all the distribution in my table (see below) which distribution I am going to user for calculation.

Here's the table.

 

DistributionDistributionNumberForecast Helper

Flat1390
Front-Loaded2219
Back-Loaded3480
Bell4475

 

What I have in mind is too use some king of variable function to select the proper distribution.

 

ex;   Distribution=
                var

                    distribution=if nationalbusinessline= all possible values, except blank() and projectduget <100000 then flat

                                          if nationnalbusinessline= bulding ,except blank() and projectbudget >50000 then bell... and so on

 

most of the information is found in the projectinfo table.  The national business line is found in the dim org table which is linked to the projectinfo table via orgskey.

 

Please note that the selection of the distribution will be based on the value of 8 fields.

Consider a switch function instead of a if function.  How do we say if all the distinct national business line found in dim org in a if or switch condition.

 

Regards.

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

 

Best regards,

 

Community Support Team _ zhenbw

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

View solution in original post

4 REPLIES 4
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

 

Best regards,

 

Community Support Team _ zhenbw

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

v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Sorry for that your file is not intuitive.

Do you want to add a calculate column to show "Flat", "Bell"..... based on some conditions?

 

Maybe you can try this formula,

 

Column =
SWITCH (
    TRUE (),
    [nationalbusinessline] <> BLANK ()
        && [projectduget] < 100000, "flat",
    [nationalbusinessline] = "bulding"
        && [nationalbusinessline] <> BLANK ()
        && [projectduget] > 50000, "bell"
)

 

If it doesn't meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

 

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

 

Best regards,

 

Community Support Team _ zhenbw

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

amitchandak
Super User
Super User

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

I can send you a copy of the excel file where we see all the condition to select the distribution.

 

https://1drv.ms/x/s!At4hIgQFrUxYjVGMA7G_2XrXB6EZ?e=dBc4mt

 

One more thing.  In the dim org table the possible NBL are Building, Transportation, Environment, Gaz, geomatics, corporate.

in a switch function how do we say if all possible NLB values in dim org?

 

Thank for your help.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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