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

KatrienVds's avatar
KatrienVds
Frequent Visitor
4 years ago

Hi tamerj1 

I will have to keep almost all of the columns from my order table, that's why I was asking if there isn't some sort of select all function like in SQL. 

 

On a different note, 
I think a normal append won't help because in the Splitted orders we only get 3 values.
All other info such as sell-to company, bill-to company, delivery terms, payment terms, delivery location (street, postcode, city, country), order date, due date, etc are all being stored in the header. To have an idea of what all gets stored in the header: https://dynamicsdocs.com/nav/2017/w1/table/sales-header 

If I'd have to add all lets say columns manually to the other table they'll result in NULL values and I'd have to do a lookup for each in my original table? Which in this case again looks quite cumbersome for something SQL would fix with a JOIN operation.  

So either I'll have to make a mini table and add it in there or I'd have to find way to use working JOIN operations instead of UNIONS.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
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

  • KatrienVds's avatar
    KatrienVds
    Frequent Visitor
    4 years ago

    Been playing with this solution for a few days now and so far it seems to work. 
    Thank you 😊