ignore filter in measure

[Anonymous]

Hello - I have a practical question as well as a theoretical question.

I have a two tables (table1 and table2) connected to each other with a one-to-many relationship. Both tables are also connected to a date table. In my measure I am counting records in table1 that are named "Disqualified" and have a related record in table2 that are named "MQL". The issue I am having is that I have the date table as a slicer on the page and this is filtering out results from table1 when I only want to it to filter on table2. So I am wondering, can/how do I stop the date table from filtering table1 in this measure? I have tried ALL, ALLEXCEPT, but neither seems to work as expected.

Secondly, I am wondering what the best practice is regarding date tables. Should I be using a single date table and connecting many other tables to it (what I do now). It certainly keeps the number of tables down, but I sometimes run into these "unexpected results" from filtering. Or should I be creating a date table for every date I want to display in charts? Or should I be creating a set of calculated columns for every date I want more date details for? Both of these seem excessive, but maybe it's best practice?

Thanks in advance,

Rob

[lbendlin]

For your first question - make the relationship between the dates table and table 1 inactive. Very often measures do not need relationships between tables.

Second question - everybody and their dog will have a different opinion.  Check what the experts are saying 

https://www.sqlbi.com/blog/marco/2017/02/22/mark-as-date-table-in-power-bi-dax-powerbi/

and then pick your own preferred approach.

[Anonymous]

Thanks for the quick reply. Unfortuately, if I make the relationship inactive it will have affects elsewhere in the file. This could work, but requires other rejigging that I am hoping to avoid. Ideally, I would just figure it out in my measure.

Thanks for the link re: date tables. Gonna check it out!

Rob

[parry2k · Accepted solution]

@Anonymous This is what you shoot for as a best practice, and then work from there, if you have to rejig your report, then do it but follow the best practice for scalability purpose, don't be afraid to go and revisit your report, it is a common scenario as part of improvement and lifecycle of your solution.

Doing the patchwork sure gets your something very quick, nothing wrong with that but keep in mind your long term strategy and solution and follow the best practice.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡