Forum Discussion

MoreDataPlease's avatar
MoreDataPlease
Frequent Visitor
9 years ago
Solved

Dynamic Column Count Based on Date Slicer Selection

I have a claims history table with multiple rows for each claim number.  Each row provides the claim number, the status of the claim, and a date when that status occurred.  I'm trying to count the nu...
Need Help
  • danrmcallister's avatar
    danrmcallister
    9 years ago

    Here's my thought on how to tackle this.  I created your table in Excel and imported it into PBI, then did the following:

     

    • Created a date table
      DateTable = Calendar(Minx(table1,Table1[Date]),Now())
       
    • To the date table I added 3 calculated columns, purpose being to count how many claims have been opened, reopened, and closed all time as of that date:
    • AllTimeOpened = CALCULATE(
    COUNTROWS(Table1),
    FILTER(Table1,
        DateTable[Date] >= Table1[Date] &&
        Table1[Status] = "Open")
)
      AllTimeReopened = CALCULATE(
    COUNTROWS(Table1),
    FILTER(Table1,
        DateTable[Date] >= Table1[Date] &&
        Table1[Status] = "Reopened")
)
      AllTimeClosed = CALCULATE(
    COUNTROWS(Table1),
    FILTER(Table1,
        DateTable[Date] >= Table1[Date] &&
        Table1[Status] = "Closed")
)
    • Then to the date table, I added another calculated column called "Open Cases"
    • Open Cases = CALCULATE(
    VALUES(DateTable[AllTimeOpened]) +
    VALUES(DateTable[AllTimeReopened]) -
    VALUES(DateTable[AllTimeClosed] )
)

    The result of all this is that I have a count in my date table of how many actively open cases there are on any given day.  I can chart this out, but if I try to make a card/slicer it gives me inaccurate data because it sums up my column.  Instead I probably want to make it a calculated measure instead.  The measure will only be meaningful if a date slicer is used, so I wrote it as such:

     

    Open Measure = IFERROR(
	CALCULATE(
    VALUES(DateTable[AllTimeOpened]) +
    VALUES(DateTable[AllTimeReopened]) -
    VALUES(DateTable[AllTimeClosed] )
	),
	"Use Slicer")

    Might be a better way to do that.

     

    Anyway, here are the visual results:

     

     

     

    Is this on the right path of what you're looking for?

     

    Dan

danrmcallister's avatar
danrmcallister
Resolver II
9 years ago

Did you try creating a relationship between your date table and your fact table based on the date?  It should work!

MoreDataPlease's avatar
MoreDataPlease
Frequent Visitor
9 years ago

danrmcallister I think I must be missing something simple.  I have a relationship between my Date table and Fact table (link is on the Date).  I have two filters on my sheet, the first is a 'Date'[Date] filter.  The second is a FactTable[loss cause] filter.  My claims count changes if the Date filter changes (and it changes the options available on my loss cause filter).  However, nothing happens to the open claims count if I select a single specific loss cause.  With the sample you created, if you added a column in Table1 titled Loss Cause and give claim 123 a cause of "trip" and claim 456 a cause of "accident", are you able to add a second filter for Loss Cause and have it work?

  • MoreDataPlease's avatar
    MoreDataPlease
    Frequent Visitor
    9 years ago

    danrmcallister I got it to work the way I need based on your suggestions - thank you so much for your help!  I created three measures in my fact table that gave me a total of open, reopened, and closed claims (code provided for one of those measures).  Then I created a measure based on those three measures: adding total open to reopened and subtracts claims, which interacts with both a date and loss cause filter.    

    Count Closes =
       VAR EndingDate =
            MAX ( 'Date'[Date] )
    RETURN
        ( CALCULATE (COUNTROWS (FactTable), 'Date'[Date] <= EndingDate, FactTable[Status] = "Closed"))