Showing Details from Disconnected Table

DAX DAX Calculations
1

I’m stuck on an issue and not sure how to resolve. I’ve been asked to show the details and rebate values from a disconnected rebate table. The table is disconnect to avoid a many to many relationship and requires a % allocation methodology in my opinion to produce the desired output.

Please see the screenshot below for the problem and attached excel files with dimension and fact tables. I’ve only included 2020 but real case includes many years.

Rebate.pbix (59.5 KB) Rebate.xlsx (25.8 KB)

Rebate
Rebate1216×686 39.8 KB

2

Hi avalon.ds,

Interesting problem. So you want the Rebate table to be filtered when you set the market/warehouse.

You can create a new table between Warehouse (1) and Account (n) ( You can create this table in the query editor). Afterwards you pop this table in between the “Dimension Warehouse Table” and the “Disconnected Rebate Table”.

This will allow you to have both Fact Tables being filtered by 1 dimension “Dimension Warehouse Table - Market”.

Best regards,
Rens

1 Like
3

Thanks Rens. I have many dimension tables and need the rebate to be filtered based on any of those dimensions. Hence I cannot go with just a bridge table. Hence the % allocation measure.

I figured it out a few minutes ago. I basically get the % allocated from one table and apply it to the disconnected table. Seems to work when I write it as two separate measures. Where I was stuck was trying to treatas from the dimension table to the disconnected table. But the solution was to actually flip the treatas and it works!

Cool technique to implement in future use cases. I’ve listed the solution below if anyone wants it in the future.

Rebate Measure % = // Gets the % of total of every rebate on the basis of account
VAR Account_Table =
    VALUES ( 'Location'[Account] ) // get account number for location selected

VAR Sales_Rebate_by_Account =
    // get account number sales rebate total
    CALCULATE (
        SUM ( Sales[Rebate] ),
        ALL ( Sales ),
        'Location'[Account] IN Account_Table
    )
RETURN
    //gets the % of total
    DIVIDE (
        SUM ( Sales[Rebate] ),
        Sales_Rebate_by_Account
    )
`

`

Rebate Allocation = // Allocate Rebate % to disconnected table
Var Account_Table = VALUES(Location[Account])
Return
CALCULATE(
SUMX (
    Rebate,
    Rebate[Amount]
        * CALCULATE (
            [Rebate Measure %],
            TREATAS ( VALUES ( 'Rebate'[Account] ), Location[Account] )
        )
),
Rebate[Account] IN Account_Table)

Rebate.pbix (63.2 KB)

image
image860×453 27.2 KB