Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Star Schema Filtering by Project Name in Fact Table

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

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.