Multiple related to Dates table filter matrix on unrelated facts

[jgabriel1]

Hey There, strong(ish) powerquery user with moderate PowerBI skills here hoping for some help with my problem.  Hopefully I'm making sense in my description below. 

I have several opportunities tables (referencing 1 opportunities table in PowerQuery) and several service contracts tables (referencing 1 service contracts table in PowerQuery).  All are related to a custom dates table through different dates columns (created on, won on, lost on, service activated on, etc).  

I've created a matrix visual pivoting on dates from the Dates table.  I need to figure out how to add a slicer from the accounts table where there's an industry grouping field.  All of the above tables contain an "account ID" column but are not related to the account in this model.  

I'm sure there's a way to do this but frankly my brain is a little fried trying to figure out how so looking for any help.  

Thanks so much!!

[jgabriel1 · Accepted solution]

I figured it out.  It requires a second inactive relationship between account and tables.  This relationship is then referenced in a measure.  Thanks for your help!

[v-linyulu-msft]

Hi,@jgabriel1 

We are really glad to know that the problem has been solved. We're also glad that you've shared your solution in the community, it's very helpful.

Please consider accepting your post as a solution to help other members find solutions faster.

Certainly, if there is anything else we can do for you, please do not hesitate to contact us.

Best Regards,

Leroy Lu

[v-linyulu-msft]

Hi,@jgabriel1 

Regarding the issue you raised, my solution is as follows:

1.First you need to check your table relationships:

From your description, I presume that your relationship graph is a star graph, and if you need to add a slicer to the table of accounts that has from that has an industry grouping field, and through that slicer, filter all the tables, then you need to use your table of accounts that has an industry grouping field as the core of the star graph, and then you can go through the relationships of the tables, and use the date column as a slicer for, filtering.

Note that in my real-world testing, date columns in tables with relationships that have hierarchical hierarchies are used as slicers with a higher priority.

Here is the relevant documentation, I hope it will help you:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

2.Secondly, after the relationship determination is over, you need to check if the slicer you need is a date column, if not, it should be replaced with the column you need. Since each of your tables has account ID, you can also modify it to account ID as a key field to string each table as required.

3.Finally click on the slicer in the visualization object and drag your desired fields into the visualization object to create visualizations for different tables and check if the output meets your expectations.

Below is the documentation for this, we hope you find it helpful:
Slicers in Power BI - Power BI | Microsoft Learn

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Leroy Lu

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

[jgabriel1]

Thanks so much for your reply here.. I really appreciate it given my noobness :).  I don't think I was explaining the problem very well.  I've created a relationship diagram to hopefully illustrate my model.  My issue is that each of the tables relates to the dates table with a different dates field (ex. Won Opps via ClosedDate, Active Opps via ExpectedClosedDate, etc).  So relating them to the account and then account to the dates table doesn't work.  Also including an image of the matrix as it is now with all tables related to the dates table and Date being used as the row in the matrix

I suspect this has something to do with creating measures but I'm no expert with DAX measures.  but could be wrong.