Split rows using a column, and divide values.

[DaveHepler]

I have used other means to accomplish this same task but I'm running into issues with RLS and the inactive relationships.   It seems to the only way for RLS to work would be to split my rows.   Below is some example data.   I have sales data with splits by salesperson.  I can write DAX to do whatever I need with this using a relationship for a salesperson dimension table and USERELATIONSHIP for an inactive relationship for salesperson 2.   No issues there...  The report is done and a lot of work on measures is working great.  Again the RLS is what kills it.  I didn't realize that when implementing RLS it would not allow the inactive relationship I need to make salesperson 2 work.   I need to be able to use RLS to filter this table by salesperson 1 AND 2... while still having  My thought is to actually split the rows.   

Opportunity Table

ID	amount	salesperson 1	salesperson 2	split type
1	1000	A	B	70-30
2	2000	B		100
3	3000	C	A	0-100
4	4000	D	A	50-50
5	5000	D

Desired New/revised Opportunity table.  

ID	amount	salesperson
1	700	A
1	300	B
2	2000	B
3	0	C
3	3000	A
4	2000	D
4	2000	A
5	5000	D

This way I could use one relationship and RLS would work.  My measures will need to be tweaked a bit but if anything they will be less complicated.   The problem is they dont always enter values for the splits.  I just used a simple DAX switch to sanitize it but I'm somewhat convinced I need to split these rows out in Power Query and I'm not as good there.  I can "fix" the split column just fine.  I've used "unpivot columns" for a similar task but I cant wrap my head around how I would Unpivot based on the split type and actually do the math to the amount column at the same time.

I also have a Salesperson dimension table.

Salespersons

ID	Salesperson
A	Alice
B	Bob
C	Charlie
D	Dave

The actual end goal is something like this.  RLS is set to filter salesperson.  In this case "Bob" is logged in.   

Visual in Bi

Salesperson	Amount
Bob	2300

With my current set up...  if I use RLS on the opportunity to "Bob" I need it to be something like [Sales_Person_1__c] = "B" || [Sales_Person_2__c] = "B"  The problem is this also allows the other part of the split in.   Opportunity 1 was split between Alice and Bob.   So it shows something like this.

Salesperson	Amount
Bill	2300
Alice	700

Okay.. not what I need so then I try to set RLS on the Salespersons table above.  That table has two relationships though... because I have two salesperson columns in opportunity.   I have one active and one intactive and then USERELATIONSHIP for the intactive.   Hopefully this makes sense.   Its a bit complicated, at least to me.   I really need direction on either getting RLS to work or splitting out those opportunities like I mentioned at the start. 

[wdx223_Daniel · Accepted solution]

new query in dax

NewTable = SELECTCOLUMNS(GENERATE('Table',VAR _p=SUBSTITUTE(IF('Table'[split type]="",100,'Table'[split type]),"-","|") RETURN ADDCOLUMNS(GENERATESERIES(1,PATHLENGTH(_p)),"@amt",'Table'[amount]*VALUE(0&PATHITEM(_p,[Value]))/100,"@SalesPerson",IF([Value]=1,'Table'[salesperson 1],'Table'[salesperson 2]))),"ID",[ID],"Amount",[@amt],"SalesPerson",[@SalesPerson])

or transform it in M

= #table({"ID","Amount","Salesperson"},List.TransformMany(Table.ToRows(#"Changed Type"),each let a=Text.Split(_{4},"-") in if List.Count(a)=1 then {{_{1},_{2}}} else List.Transform(List.Positions(a),(x)=>{_{1}*Number.From(a{x})/100,_{x+2}}),(x,y)=>{x{0}}&y))