Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count of Discrepancy Based on Date

Hello,   We're trying to count the # of discrepancies occuring over time. The thing is, once a discrepancy has been closed, it's status appears as closed in the table (Check field Discrepancy.Statu...
Need Help
Show and Tell
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi aj1973 , 

    Add another relationship from your date table to your facts on the close date. you can then create a measure that counts the closed by date as

     

     

    closed = calculate(countrows('table'), 'date_closed' <> blank(), userelationship('date'[date], table[date_closed]))

     

richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
5 years ago

Hi aj1973 , 

Add another relationship from your date table to your facts on the close date. you can then create a measure that counts the closed by date as

 

 

closed = calculate(countrows('table'), 'date_closed' <> blank(), userelationship('date'[date], table[date_closed]))

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you so much for this! It is working as expected. I'm getting the correct results for Closed Cases. How then, can I use a similar formula to capture Open Discrepancies?

Will it be Open = Calculate(Countrows('discrepancyTable'), 'OpenDateTime' <> Blank, Userelationship('date'[date], table[OpenDateTime]))??

 

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Hi Anonymous ,

     

    If the active relationship between the date table and the discrepancy table is materialized through the opendatetime column then you would only need the following

    Open = Countrows('discrepancyTable')

    This will count all of the rows when they where opened regardless of state.

     

    Make Sense?

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Hi Anonymous ,

     

    Not sure why you un-accepted the solution, if possible could you accept the solution as it makes it easier for other users to find answers in the forum.

     

    Thanks,