Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Icon for Post Partisan rankPost Partisan
4 years ago

3 fact tables

Hi guys

I have 3 fact tables in my model: sales, receipts and pendings. 
They all have the same column, Doc#, which is a unique key. I want to be able to, through a visual filter, choose the document number (Doc#) and then get all the information about sales, pendings and receipts.
I've created a 4th table with just the Doc# column, no duplicates and tried to connect it to those 3 fact tables, plus the calendar table. Except this last table, all the connections are inactive.
1st question: i belive those connections are inactive because those 3 fact tables are all connected to the calendar table. Is that so?
2nd question: How would you guys solve this issue
Thanks in advance all of you
P.S. Model attached

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    pedroccamaraDBI , If the invoice is a dimension then you should not join that with invoice.  In case you need to filter invoice slicer or some measure you can create a measure like

     

    new measure =
    var _max = maxx(allselected(Date),Date[Date])
    var _min = minx(allselected(Date),Date[Date])
    return
    calculate( sum(invoice[Value]), filter('invoice', 'invoice'[Date] >=_min && 'invoice'[Date] <=_max))

     

    Thump rule. Try not to join dimensions with each other, they might create loops

     

    Try to remove that join and activate others and check

    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Icon for Post Partisan rankPost Partisan
      4 years ago

      Hello amitchandak 
      First of all, many thanks for your answer.
      I'm not sure if i understand your solution. Let me be more clear.
      I've create a table with unique invoices with their dates. That table is called invoices on the pic above.
      I didn't understand where to put the measure and how to connect it to all 3 fact tables. 
      What i need is after filtering the invoice slicer, (A) which is that invoice table created,  i get all data for each fact table with THAT invoice.
      Like this image


      Could you explain me better?
      Thanks a lot Amit