Forum Discussion

KatrienVds's avatar
KatrienVds
Frequent Visitor
4 years ago
Solved

Splitting data out over a predefined percentage and using that data for new calculations

Hi BI Community   I'm having difficulties to visualize an important feature in our reports. I used to have the data in a fully different format, in the new environment I'm unable to do SQL queries ...
database
DAX
DAX Calculation
percentage
Power BI Desktop
Power Query
  • tamerj1's avatar
    tamerj1
    4 years ago

    KatrienVds 
    No need to select all columns. We will follow your strategy. Use this code to generate the appended header table.

    Order Header 2 = 
VAR Owner1Table =
    SELECTCOLUMNS ( 
        'Splitted orders 1', 
        "@Order No", 'Splitted orders 1'[Order No], 
        "@Owner", 'Splitted orders 1'[Owner 1], 
        "@Percentage", 'Splitted orders 1'[Percentage] 
    )
VAR Owner2Table =
    SELECTCOLUMNS ( 
        'Splitted orders 1', 
        "@Order No", 'Splitted orders 1'[Order No], 
        "@Owner", 'Splitted orders 1'[Owner 2], 
        "@Percentage", 1 - 'Splitted orders 1'[Percentage] 
    )
VAR SplittedOrders =
    UNION ( Owner1Table, Owner2Table )
VAR OrderHeader = 
    SELECTCOLUMNS ( 'Order Header', "Order No", 'Order Header'[Order No], "Owner", 'Order Header'[Owner] )
VAR NotSplittedOrders =
    ADDCOLUMNS ( FILTER ( OrderHeader, NOT ( [Order No] IN VALUES ( 'Splitted orders 1'[Order No] ) ) ), "Percentage", 1 )
VAR Result =
    UNION ( NotSplittedOrders, SplittedOrders )
RETURN
    Result

    Then make tthis table in between the two tables exactly as you have suggested

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

KatrienVds 

Your description is amazing!

I believe you've almost achieved it. We just need to make sure that the full joined table between header and split is the correct one then we can create many-many relationship between the full joint table and the lines table. The rest is simple. Can you please write the names of the columns you need to keep from each table? Thanks and have a great day!

KatrienVds's avatar
KatrienVds
Frequent Visitor
4 years ago

Hi tamerj1 

I really hope so as I've tried plenty things before so far without succes.

I need all columns from my header table (which are actually quite a few to write down).
Isn't there some sort of get all like there is in SQL? 


From the second split table (to use the names from my example) :
Order No = Order No on the header (so the split ones will be double)

Owner = Owner on the order header 

Percentage is the only "extra" column that would appear to the header