cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

Conditional Column (Variable)

Good afternoon,

I had one column for Office which had 1-4 offices separated by a comma; therefore, I split by comma delimiter to create the necessary splits to do percentages on the number of Offices involved.

Since the maximum amount of Offices involved is 4, I am assigning 25% to each column when I do my calculations.

If there is not more than one office it produced a null which I handled through the creation of conditional columns in PowerQuery for Office 2 - 4.

If one office is involved the conditional columns allow for that office to occupy all 4 rows for full ownership (1) of the JobOrder.

If 2 offices are involved, they each occupy 2 of the 4 rows to receive half ownership (.5) of the JobOrder .

If 4 offices are involved, they each receive 1 of the 4 rows to receive quarter ownership (.25) of the JobOrder.

The only scenario I am having difficulty is when there are 3 offices involved because of my 4th split columns conditional logic that handles all other scenarios above.

I'd be utilizing the Office.1, Office.2.Split, Office.3.Split, and Office.4.Split columns as these handle the bulk of the scenarios, would I just have to handle the 3 office scenario through the original split columns?

Here's the logic for the Office.4.Split as well:

I appreciate and value your input.

Have a great Friday.

1 ACCEPTED SOLUTION

Accepted Solutions
MVP

Re: Conditional Column (Variable)

@sirlanceohlott solution attached and I hope that is what you are looking for.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

3 REPLIES 3
MVP

Re: Conditional Column (Variable)

@sirlanceohlott I thnk you are taking different approach, you should split offices in rows and then thru DAX calculate the % based on # of office on a job. If you are not sure how to do this, share sample pbix with data and I will get you the solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Regular Visitor

Re: Conditional Column (Variable)

@parry2k , I appreciate your help this morning as well as this afternoon.

I only took a screenshot of the specific columns within that fact table, but I trust your judgement on a different approach.

I'll send you a message separately with the information needed for you to review.

MVP

Re: Conditional Column (Variable)

@sirlanceohlott solution attached and I hope that is what you are looking for.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,209)