Forum Discussion

markblom's avatar
markblom
Frequent Visitor
3 years ago
Solved

Combine data from hierarchy

Hi,   Let's say I have these 3 tables:   Article ArticleId ArticleCode ArticleSecondary PrimaryArticleId SecondaryArticleId This table is the link between a Primary Article with zero, o...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi markblom 

    please try

    Quantity Ordered For Secondary Article =
    CALCULATE (
    SUM ( Orders[Quantity] ),
    USERELATIONSHIP ( ArticleSecundary[SecondaryArticleId], Orders[ArticleId] ),
    CROSSFILTER ( Article[ArticleId], Orders[ArticleId], NONE )
    )

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

Hi markblom 

please try

Quantity Ordered For Secondary Article =
CALCULATE (
SUM ( Orders[Quantity] ),
USERELATIONSHIP ( ArticleSecundary[SecondaryArticleId], Orders[ArticleId] ),
CROSSFILTER ( Article[ArticleId], Orders[ArticleId], NONE )
)

  • markblom's avatar
    markblom
    Frequent Visitor
    3 years ago

    This is working for me, thanks!!!

    One minor issue though:

    First row contains a value for Secondary orders quantity but no ArticleCode. Any idea what is causing this?

     

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      markblom 
      Power Bi automatically creates a blank row to group the quanities that do not belong to any secondary id. Just filter out the blank row using the filter pane.