Forum Discussion

chat_peters's avatar
chat_peters
Icon for Helper III rankHelper III
4 years ago
Solved

Aggregation with USERELATIONSHIP

I have data model with 4 tables (I don't have much freedom to make changes to it)  Order Fact, Buyer Dim, Caller Dim and People Dim. Sometimes the buyer and the caller can be the same person and som...
Need Help
Tips and Tricks
  • Icey's avatar
    Icey
    4 years ago

    Hi chat_peters ,

     

    Is what you want something like this?

    Measure =
COUNT ( 'Order Fact'[Order ID] )
    + CALCULATE (
        COUNT ( 'Order Fact'[Order ID] ),
        USERELATIONSHIP ( 'PeopleDim'[Id], BuyerDim[BuyerID] ),
        'Order Fact'[Caller different Buyer] = 1
    )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

chat_peters's avatar
chat_peters
Icon for Helper III rankHelper III
4 years ago

This solution works great. I am going with Icey's solution for now because it works with all the other filters and in the direct query mode and the outdated version of power bi I am working with. I made tiny tweak to the formula 

Measure =
CALCULATE(COUNT( 'Order Fact'[Order ID] ), 'Order Fact'[Caller Buyer Same] =1)
    + CALCULATE (
        COUNT ( 'Order Fact'[Order ID] ),
        USERELATIONSHIP ( 'PeopleDim'[Id], BuyerDim[BuyerID] ),
        'Order Fact'[Caller different Buyer] = 1
    )

Thank you Icey so much😊