Forum Discussion

Henry1943's avatar
Henry1943
Frequent Visitor
8 years ago
Solved

Filter cross different tables

I met the problem as below, like I have a table 1 information as below   Name     receive Date   Category 1             2017-10-10         A 2             2017-10-12         A 3             2017...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    A couple of ways you could do this then.

     

    You could have a core date table where the dates are hinged to and then have inactive relationships between this Date table to each one of the columns in your fact table. Then you can make a measure for each of the dates using this method

     

    receuptQty= CALCULATE(COUNTROWS(table1), USERELATIONSHIP(dates[The Date], table1[Receipt Date]))

    uploadQty= CALCULATE(COUNTROWS(table1), USERELATIONSHIP(dates[The Date], table1[Upload Date]))

     

    etc...

     

    Then bring each one of these measures into your visual.

     

    Depending on how you have your stuff setup this might be a sensible route, if not, you could unpivot the dates so you make a table looking like this

     

    pivotedTable 

    DATE       DATE TYPE         CATEGORY    VALUE

    10/10       ReceiptDate      A                    3

    10/10       UploadDate      B                     4

    11/10       ReceiptDate      A                    5

    11/10       UploadDate      A                    7

    11/10       UploadDate      B                    5

     

    Then make measures like this

    CALCULATE(SUM(pivotedTable[Value]), FILTER('pivotedTable, pivotedTable[Date Type] = "ReceiptDate"))

    CALCULATE(SUM(

Anonymous's avatar
Anonymous
Not applicable
8 years ago
If you make a measure for countrows(table1) then you shouldn't need table 2 at all. You should be able to do it with just that.
Henry1943's avatar
Henry1943
Frequent Visitor
8 years ago

Thank you, I tried taht, but I need to have a calendar and the X-axis should be the continious date like from the 1-Oct to 30-Oct, this is the reason I create a calendar table and count the quantity

So, if with measure, how could i do?

like quantity=countrows(table1) and then the receive date as X-axis?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    As long as date is formatted as a date and not a string it should be able to become a continuous value on the X axis. And yes, how you have written the measure formula looks perfect.
    • Henry1943's avatar
      Henry1943
      Frequent Visitor
      8 years ago

      Thank you for your prompt reply and that really helps a lot, but I am still a little confusion, as I mentioned, those data are just parts of it and I have the data 2, date 3 colunm, and what I want is the table as below

      and even the mesure is a good idea but I can't get chart like it, and I am wondering if we have other masure help me to achive that or could the filter cross tables.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        To clarify then...

         

        Is "plan receive quantity", "receipt quantity", "upload quantity", "Approve quantity" all categories in your original example?

        And what is the x axis, as that is not a Date (unless that is hooked up to a Date table and the axis is showing weeks.

         

        Or have I mis-understood entirely?