Forum Discussion
Using date dim and filtering on different fact
Hello,
I have a modeling problem I cannot seem to find a good approach for. I have two fact tables. One is for inspections conducted and another is actions, which are derived from within an inspection. My current data model is as follows. Inspections filters Actions and is a 1:M relationship on audit_id. I also have a date_dim related to inspections on completed date.
Here is what I am trying to solve. I want to be able to use my date_dim and filter a table of actions based off of when the action was completed. Right now the filter is based off of when the inspection was completed and not when the action was completed. When I create a relationship between actions and the date_dim I will get an ambiguity error.
I would still like to use columns from my inspections table because it includes the location of the actions. How would I be able to allow the user to use the date_dim table to filter based off of a date, but the actions table filters on the action[completed date] and not on the inspection[completed date]?
Can this be solved using a physical relationship? Do I need a bridge table? Or do I need to create a virtual relationship using treatas or some other DAX function?
Glad to provide more information if my above explanation is not clear.
2 Replies
You can create and inactive relationship between DimDate and Actions the activate it in the measures, something like this.
Action Count = CALCULATE ( COUNTROWS ( 'Action Table' ), USERELATIONSHIP ( 'Action Table'[Action Date], Dim_Date[Date] ) )Here is an article on USERELATIONSHIP to help you out.
https://www.sqlbi.com/articles/using-userelationship-in-dax/jdbuchanan71 Can you excplain how that would work in a standared table that shows attributed from both tables? i.e.
action id audit id action completed date action status inspection location
