Forum Discussion
Solved
Split rows using a column, and divide values.
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...
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))
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))