Forum Discussion

PaisleyPrince's avatar
PaisleyPrince
Icon for Advocate II rankAdvocate II
9 months ago
Solved

Filter two fact tables via the same date table

Hi, I have a single date table which is used to filter a fact table with a project start date and the total fees applicable to that project over a period of years. I now have a new fact table to add...
Need Help
  • Kejmil's avatar
    Kejmil
    9 months ago

    Hi,
    The best approach:
    FactA -> DataTable

    FactB -> DataTable

    Requirements:
    proper key between both tables needed.
    cardinality (many to one (*:1) (date table needs to have unique key)

    Cross-filter direction:
    Single

    Make this relationship active: Yes

     

HarishKM's avatar
HarishKM
Icon for Super User rankSuper User
8 months ago

PaisleyPrince  Hey,

  • Keep relationships from the single Date table to each fact inactive.
  • Add a selector table: DateType = { "Project Start", "Invoice Date" } and a slicer on it.
  • Wrap measures with USERELATIONSHIP to activate the chosen date context:
  • Example:
- Relationships (inactive):
- Date[Date] — Projects[StartDate] (inactive)
- Date[Date] — Invoices[InvoiceDate] (inactive)
- Selector:
- DateType = DATATABLE("Type", STRING, {{"Project Start"}, {"Invoice Date"}})
- Measures:

Fees by Selected Date =
VAR sel = SELECTEDVALUE(DateType[Type], "Project Start")
RETURN
SWITCH(
sel,
"Invoice Date", CALCULATE([Total Fees Invoices], USERELATIONSHIP(Date[Date], Invoices[InvoiceDate])),
CALCULATE([Total Fees Projects], USERELATIONSHIP(Date[Date], Projects[StartDate]))
)​

Thanks

Haish K 

If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.