CODEWITHSUNDEEP
excelpowerpivotdax
Kevin
Kevin

Reputation: 59

Use RELATED and FILTER to cross reference two tables in DAX?

I have two tables in power pivot, a order header and a order detail. I would like to identify on the header, with a true/false flag, if a particular class of item is on the order detail.

Order Header Table 

Order ID    Order Amt   *Has HBG*
100041328   10.24        1
100041329   34.57        0
100041330   25.89        1

Order Detail Table 

Order ID    SKU       Dept
100041328   1749988   HBG
100041328   9102272   SAC
100041329   1325344   SOA
100041329   4255337   LAF
100041329   7541744   SOC
100041330   7862194   HBG
100041330   3829646   HAS
100041330   3399937   EAR

I'm using the order ID to establish a relationship between the two tables. I should think that some sort of combination of FILTER and RELATED would allow me to flagorder 100041328 and 100041330 as having products in the HBG department but I keep getting errors. Any thoughts on how these could be flagged?

Upvotes: 2

Views: 726

Answers (1)

Rory
Rory

Reputation: 969

You can count the number of rows on the detail table using COUNTROWS inside a CALCULATE statement which changes the row context to filter context. Add an additional filter for Dept and return 1 or 0 depending on how many entries have been found. You can display the 1 or 0 as True/False. 

=
IF (
    CALCULATE (
        COUNTROWS ( DetailsTable ),
        DetailsTable[Dept] = "HBG"
    )
        > 0,
    1,
    0
)

Upvotes: 2

Related Questions