Forum Discussion

RenateBK's avatar
RenateBK
Icon for Helper II rankHelper II
11 months ago
Solved

Cumulative Distinct Count per year, per category, query exceeds available resources issue

Hello,    I'm working with a data set where I need to calculate the cumulative distinctcount per category per year and I'm running into an issue of query time out/exceeding available resources.   ...
Need Help
  • amitchandak's avatar
    amitchandak
    11 months ago

    RenateBK , try like 

     

    AmountCases = CALCULATE(Countrows(Values(Table1[CaseID]) ))

     

    or

     

    AmountCases = CALCULATE(Countrows(Summmarize(filter(Table1, Table1[CaseID] <> Blank()), [CaseID])  ))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
11 months ago

RenateBK , try like 

 

AmountCases = CALCULATE(Countrows(Values(Table1[CaseID]) ))

 

or

 

AmountCases = CALCULATE(Countrows(Summmarize(filter(Table1, Table1[CaseID] <> Blank()), [CaseID])  ))

  • RenateBK's avatar
    RenateBK
    Icon for Helper II rankHelper II
    11 months ago

    Hello, 

    I doubledchecked it, and there's a minor difference in numbers, with distinctcountnoblank I get 563 945 and with the your countrows formula it returns 563 969, so only 14 cases more, these seems to be related to older cases that are uncategorized. 

     

    This formula together with updated my timedate table to properly being categorized as a date table in my relationshipsmodel solved my issue.

     

    Thank you for the help!