Level of detail between tables

[Szokens]

Hi, stuck with one thing Im trying to build. Here is my semantic model. Data is limited to range 29 March 2024 to 3 April 2024.

 What I want to achieve is field name New/Old user quarter (shall be named actually New/Old Team quarter 😉 explanation below). Field will have two values/flags which will change under following condition: IF(related(last_day_of_quarter)> min sent date quarter,"Returning","New")

Very important thing here is field min sent date quarter. This shall be computed on Signature Team ID level. So we are looking for minimum value of last_day_of_quarter for each Signature Team ID separately. I have prepared in DAX two calculated columns with the same formulas. However only this one build in BODI table works well while one in dmn_email table gives fake results. Formulas are shown below.

On sample data below I highlighted how mentioned logic shall work when team sent email first time at all in Q12024 and then in Q22024 email has been sent by team too.

I want to build kind of viz (sample below) with dimension of calendar_yyyyqn, count distinct of Signature Team IDs as measure and put as a legend my aim field New/Old Team quarter.

Will be grateful for help.

 

 

[Szokens]

thanks @timalbers , do you suggest to merge dmn_email with BODI tables instead of creating relationship between them?

Thanks @Anonymous , does exist any formula to bring necessary BODI table filters if they are present? Which potentially might correct my calculation?

[timalbers]

As I've said, it's hard to tell without the full picture of your data model schema and all the columns.
It depends on what each table represents.

Out of your screenshot I assume dmn_email consists of sent emails, so this would be the fact table. Then you have a date table, a receipients table and a employee table, which are all dimension tables that serve the fact table with further information about the regarding dimension. But I'm afraid that I don't know exactly what the purpose of the BODi table is... If it is an additional dimension table (maybe branch?) then you'll have to have a unique key for each branch in this table that you can connect to the fact table.

[Anonymous]

Hi @Szokens ,

Thanks to lbendlin and timalbers for their quick replies. I have some other thoughts to add:

The `ALLEXCEPT` function removes all filters from the BODI table except for the `Signature Team ID`. When you use this in the BODI table, it works as expected because the context is clear. However, when you use the same formula in the `dmn_email` table, the context might be different due to the many-to-many relationship with the BODI table.

In a many-to-many relationship, filters can propagate in unexpected ways.The context in which the formula is evaluated can differ between tables. In the `dmn_email` table, the context might not include the necessary filters from the `BODI` table, leading to incorrect results.

Best Regards,

Neeko Tang

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

[timalbers · Accepted solution]

 Hi @Szokens ,

it's hard to tell what exactly the issue is, without knowing how your tables are connected.

But at a glance I can tell you that sooner or later you will run into problems using that many-to-many relationship between the BODI and dmn_email table. It looks like the dmn_email table is your fact table, so I guess the BODI table is a dimension table just like the others? Is there a unique key column in the BODI table, which you can connect to the dmn_email table? Use it instead.

m:m relationships aren't best practice for a proper Power BI data model and often result in unexpected calculation results. If you're lucky this already helps to eliminate your issue.

Cheers

Tim

[Szokens]

Hi @timalbers 

Thanks, I have use your idea to merge dmn_email table and BODI table into one, leaving rest of semantic model as it is. 

Fortunately this operation and rest of my calculated columns / measures in these tables didn't negatively impact of data import, so I was able to use DAX formula with ALLEXCEPT() function to achieve my goal.

"m:m relationships aren't best practice for a proper Power BI data model and often result in unexpected calculation results. If you're lucky this already helps to eliminate your issue." 

This is interesting, can you please elaborate more? I have heard that I shall stick mostly with relationships and semantic model when building source, and keepeing away of merging tables always when possible. I have also faced some problems recently when I have merged to many tables and importing of data just took dozen hours.

[lbendlin]

Power BI prefers a star schema data model.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...