Star Schema Filtering by Project Name in Fact Table

[Anonymous]

Hello, I have the above star schema. However on one of my pages there needs to be a filter on "Project Name". 
Project Name appears in my Fact table. So unless i enable bidirectional filtering between all dim tables my project name column will not filter my data correctly. 

I have tried creating a "DIM Project" table that contains the project name and an index that I have generated with Power Query:

I have then merged this table to my fact table on the project name and only kept the Index in my fact table. 
The model now looks like this:

Relationship:

I thought this would allow me to filter by Project name but this for some reason does not link to any of my other data and when I filter on my report nothing changes when I filter by this Project Name from DIM Project.

Can anyone help me figure out why?

Thanks

[Anonymous]

Hi @Anonymous 

You can use crossfilter() function in a measure to implement the filter

e.g 

Measure =
CALCULATE (
    SUM ( table[sales] ),
    CROSSFILTER ( dimtable[projectname], facttable[projectname], BOTH )
)

You can refer to the following link about more of the function.

CROSSFILTER function - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

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

[Anonymous]

Sorry, i am confused. I have no sales data in my model, only project management data (project names etc). 
What is your suggested outcome here?

If i write a correct dax measure will i be able to turn my relationships to single direction in my data model? 

Not sure where you're going with this one, any help would be appreciated.

Thanks for your reply 🙂 
Kind regards

AD

[Anonymous]

Hi @Anonymous 

The code just the sample, I meant you can use the crossfilter() function. and you can set the crossfilter to single when you use the funciton.

Best Regards!

Yolo Zhu

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

[Anonymous]

Sorry, i don't think this answers my question.

How does this help me set my relationships to single in the data model and still be able to filter by project name from my fact table? My problem is that i have a star schema but i want to be able to filter by the project name in the fact table. To do this currently, i have to set all my relationships to bi-directional so that the fact table can filter values from the dim tables, which is not how a sound star schema is meant to work. 

My question is how do i approach solving this issue, i need to filter by the project name (in fact table) AND have my relationships between fact and dim tables as single direction. 

I'm not sure how the crossfilter function is meant to help here... I need my project name slicer to filter my data.

[Anonymous]

Bump