Forum Discussion

RishabSajith's avatar
RishabSajith
Frequent Visitor
8 months ago
Solved

Issue with the DAX with data model in Power BI - Distinct Count

Hello,

 

I have run into the following issue in power BI, im still a beginner using Power BI:

I have 3 tables, 1 is deal table which includes all the information about the deal such ( number of bedrooms, washrooms, priceof the property,type of property, etc. ), next table is deal_users which includes all the information about the commission earned by the user for a particular deal and users table which includes the information of all the users. Currently the data model is as seen below:

Following is the relatonship between the tables:

1. many-to-one relationship between deal_id (deal_user) and id (deals)

2. many-to-one relationship between user_id(deals) and id (users)

3. one-to-many relationship between id(users) and user_id(deal_users) ( inactive )

 

However, I can only see the gross commission for some of the deals that happened, but I cant see the deal value which is Deal Value = SUM('newcrm_prod bixo_deals'[price]) for some deals only, even though the Ids match between the tables:

But the Ids are present in both deals and deal_users, following is the check I did manually in deal_user table using:

 

The DAX formula for all the calculations are as follows:

Number of Deals = DISTINCTCOUNT('newcrm_prod bixo_deal_user'[deal_id])
Deal Value = SUM('newcrm_prod bixo_deals'[price])
Gross Commission (Active User Relationship) =
CALCULATE(
    SUM('newcrm_prod bixo_deal_user'[total_amount]),
    USERELATIONSHIP(
        'newcrm_prod bixo_deal_user'[user_id],
        'newcrm_prod users'[id]
    )
)

 

What is the issue here? Am I missing something? Can someone help me with the same as I can see that these Ids exist is both the tables with the same data types as well as without any hidden characters.

 

Thanks in advance.

Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    8 months ago

    RishabSajith ,

    , User table should join Either with Deal or Deal/User, not both.

    Possible join

    User-> Deal -> Deal user.

    I can see both deal ID and user ID in deal and deal user, then there should be a composite key and a join be like

    user -> deal user -> Deal

    Deal user and Deal can many to many and Deal user filter Deal

8 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    8 months ago

    The picture you show is only part of a bigger data model and the table names are not the ones you cite, so it is a bit confusing. We need to see the entire data model to see other relationships that might cause the issue.

     

    That said, can you share the pbix so I fix the issue? It is impossible other wise to help you

     

    If you can, please send it with a private message (and inform me you did answering here)

     

    Thanks

     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

  • RishabSajith's avatar
    RishabSajith
    Frequent Visitor
    8 months ago

    Hello FBergamaschi ,

     

    Unfortunately I cannot share the whole PBIX with you as it contains sensitive information, however this is the entire data model with the corrected names I mentioned:

     

    Hope this helps.

    • amitchandak's avatar
      amitchandak
      Super User
      8 months ago

      RishabSajith ,

      , User table should join Either with Deal or Deal/User, not both.

      Possible join

      User-> Deal -> Deal user.

      I can see both deal ID and user ID in deal and deal user, then there should be a composite key and a join be like

      user -> deal user -> Deal

      Deal user and Deal can many to many and Deal user filter Deal

      • RishabSajith's avatar
        RishabSajith
        Frequent Visitor
        8 months ago

        Hello amitchandak ,

        Thanks for the help.

         

        This helps to an extend, however I when I add "ref" which is the unique identifier in the deals table, it seems to be pulling all the "ref" which are not a part of the filter as well.

         

         

        Following is what is there in this Matrix table:

         

         

    • FBergamaschi's avatar
      FBergamaschi
      Super User
      8 months ago

      The user table has too many relationships: 

       

      Users -> Deals -> Deal User

       

      User -> Deal User

       

      You need only one of these in place at a time, amybe changing it according to your needs

       

      Without the pbix I cannot do more, unfortunately that give you rough suggestions

       

      If this helped, please consider giving kudos and mark as a solution

      me in replies or I'll lose your thread

      Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

      Consider voting this Power BI idea

      Francesco Bergamaschi

      MBA, M.Eng, M.Econ, Professor of BI

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Super User
    8 months ago

    Hi RishabSajith,

    Wanted to let you know your relatinship creates a problem. 

    When you put a field from deal_user (example: deal_user[deal_id]) in a visual:

    • Power BI filters deal_user
    • That filter cannot travel backward to deals (because the active direction goes only from deals → deal_user, not the reverse)

    Can you try to mdify the Deal Value measure as below and let me know.

    Deal Value =
CALCULATE(
    SUM('newcrm_prod bixo_deals'[price]),
    CROSSFILTER(
        'newcrm_prod bixo_deal_user'[deal_id],
        'newcrm_prod bixo_deals'[id],
        BOTH)
)

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.