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

[RenateBK]

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.

It must be distinctcount as the caseID can be repeated several times in one year. The case will be registrered with different status as it goes through the casehandling process in the system and I only want to count the most recent status based on the latest statusupdate date)

The timetable is set up in Fabric and is therefore not created based on the dates in the dataset I'm using, but has a set timeframe which can be extended basically indefinitely, currently it includes all dates from 1980 up to 2050. Furthermore, to make it possible to link the dates in different tables to the main date table, the dates in my dataset has been converted to a DATEKEY consisting of 8 integers, instead of a date to ensure there's no issues with the formatting and establishing relationships.   

The issue I come across is that my query resources gets exhausted when I try to calculate the cumulative distinctcount per year, per category.

This is currently what I have:

Measure 1:

AmountCases = CALCULATE(DISTINCTCOUNTNOBLANK(Table1[CaseID])

Measure 2:

CumulativeAmount = CALCULATE(

    [AmountCases],

    FILTER(

        ALL(dim_date_sl[full_date]),

        dim_date_sl[full_date] <= MAX (dim_date_sl[full_date])

            && YEAR(dim_date_sl[full_date]) = MAX(dim_date_sl[year])))

Technically, I don't need to calculate the Cumulative Count per year more than 5 years back, but I cannot figure out how to limit the calculations so that I can calculate it appropriately without exhausting avilable resources. 

Is there a better way to write the DAX or another way to solve this problem?

Thank you so much,

Renate 

[v-hjannapu]

Hi @RenateBK,

I would also take a moment to thank @Ashish_Mathur  , @amitchandak  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @RenateBK,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[Ashish_Mathur]

Hi,

Does this work any better?

=calculate(distinctcount('Table1'[CaseID]),datesytd(calendar[date],"31/12"))

[amitchandak · Accepted solution]

@RenateBK , try like 

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

or

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

[RenateBK]

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!