Forum Discussion

Peter_23's avatar
Peter_23
Advocate V
3 days ago

left join dax

I have two tables store and clients; no relations between them. I must not have physical relationship, I prefer virtual.

And the report have a many  slicers:  Year, region, country from clients. So the visual have four columns.

region (clients)Country (clients)sales (clients)inventory (store)

and table store have

year

regionCountryinventory 

Dax measure: 

NATURALLEFTOUTERJOIN (store, clients)

The principal idea is inventory is actually a measure (e.g. left outer join) to get the total in each sublevel: country, region passing the selected value from slicer.

Actually I get the total from all years in measure "inventory". I don't get the context to compute (left outer join) :( . I figure out is about the missing relation between tables. 

Any ideas?

 

thanks in advance.

 

Interesting Links
Need Help
Tips and Tricks
Tutorial Requests

3 Replies

  • danextian's avatar
    danextian
    Super User
    3 days ago

    Hi Peter_23​ 

    You can use TREATAS:CALCULATE( [Inventory Measure], TREATAS( VALUES(Slicer[Region]), Fact[Region] ) )

    If this doesn’t work, please provide a sample dataset along with the expected result based on that data. Please provide it as text/table format, not as an image, so I can clearly understand the intended outcome.

     

  • ShahRukhSameer's avatar
    ShahRukhSameer
    Responsive Resident
    3 days ago

    Hi Peter_23​,

    I wouldn't use NATURALLEFTOUTERJOIN for this. Since you want to keep the tables disconnected, TREATAS is a better fit.

    Try:

    Inventory =
    CALCULATE (
    SUM ( Store[Inventory] ),
    TREATAS ( VALUES ( Clients[Year] ), Store[Year] ),
    TREATAS ( VALUES ( Clients[Region] ), Store[Region] ),
    TREATAS ( VALUES ( Clients[Country] ), Store[Country] )
    )

    Then use Clients[Region] and Clients[Country] in the visual along with [Inventory].

    TREATAS will transfer the current filter context from Clients to Store, so the inventory should respond to the slicers and the row context without needing a physical relationship.

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    3 days ago

    Hi Peter_23​ - 

    If Year + Region + Country is a composite business key, I would use a multi-column TREATAS instead

    Inventory := CALCULATE ( SUM ( Store[Inventory] ),

    TREATAS (SUMMARIZE ( Clients, Clients[Year], Clients[Region], Clients[Country] ),

    Store[Year], Store[Region], Store[Country] ) )

    This is safer when you need to preserve the actual Year–Region–Country combinations.

    Hope this helps.