cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sirlanceohlott Regular Visitor
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? 

 

ConditionalColumn(1).PNG

 

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

 

ConditionalColumn(2).PNG

 

I appreciate and value your input.

 

Have a great Friday.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Conditional Column (Variable)

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






Did I answer your question? Mark my post as a solution.

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





View solution in original post

3 REPLIES 3

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.






Did I answer your question? Mark my post as a solution.

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





sirlanceohlott Regular Visitor
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. 

Re: Conditional Column (Variable)

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






Did I answer your question? Mark my post as a solution.

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





View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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