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

[RishabSajith]

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.

[ajaybabuinturi]

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.

[RishabSajith]

Hello ,

This solution does not seem to work.

[RishabSajith]

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.

[FBergamaschi]

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

[amitchandak · Accepted solution]

@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]

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:

[RishabSajith]

Just tried it again, this seems to work!

Thank you!

[FBergamaschi]

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