Forum Discussion

sgraflund's avatar
sgraflund
New Member
5 years ago

Filtering Disposition Categories between Tables

I've got 2 tables that store data differently in reference to invoice aging.

 

The 1st table is the "Summary" table, that has the "Diff" columns moved to the "0-30" columns.  I had to build this through straight SQL Temp Tables to move the data into the right table, Matrix wouldn't work.

 

The 2nd table is the original Matrix table with the diff columns in place.  Now you can see in the table the disposition categories are "Adjustment, New, No Change, Moved to Next Bucket, and Payments/Transfer.  Clicking on any one of those disposition categories, or buckets, filters the detail data correctly.  Clicking on the header "Total" rows for each month does not filter the detail .. because there is no link to the associated "Disposition" column in the detail. 

 

I have setup a dimDisposition table to do this filtering .. 

I have a factDispositionSummary and a factDispositionDetail table, both link to the dimDisposition and standard dimDate tables.

 

How can I get that top "Total" row to filter down to the Details?  That's my conundrum.

 

Thanks!

 

 

 

That part works .. 

 

 

 

Need Help

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    sgraflund  I'm confused on what you are trying to do here with the total row - what filter should it apply? Doesn't it show all? Is there any relationship between the two tables you have created? Can you share the model view please too?

    • sgraflund's avatar
      sgraflund
      New Member
      5 years ago

      Disposition is the fact table that stores the details.  0-30New was renamed to "0-30" in the middle matrix.  It's grouped by TransDate / Disposition in the matrix.  You've got 0-30Old, 0-30New, 0-30Diff, 31-60Old, 31-60New, 31-60Diff.. etc.

       

      Now.. the way they want the data displayed is they want the totals for 0-30New at the top.  But then they want to move the 0-30Diff buckets under the 0-30New totals.  (I warned them this may not be possible with Power BI .. but they wanted it).

       

      So, I had to write a separate disposition summary table.  All in SQL, I summed up the 0-30New fields and grouped by trans date and inserted them into a temp table.  Then I took the Diff fields, grouped by trans date and disposition and inserted them into that temp table.  

       

      I don't think it's possible because there is no relationship between the disposition and date columns for the header " Total" row.  The buckets work fine in the columns where the disposition matches.