topic Re: DAX expression to get records that have not been updated in DAX Commands and Tips

DAX expression to get records that have not been updated

rsrajkumar — Mon, 23 Dec 2024 21:56:44 GMT

Hi,

For the below table, I would like to know the 'Case Number' that have not been updated for the past 10 days by looking at the 'Modified Date' column .

Case Number	Created Date	Modified Date
15606704	11-Dec-24	11-Dec-24
15611766	10-Dec-24	12-Dec-24
15611766	10-Dec-24	11-Dec-24
15616992	10-Dec-24	23-Dec-24
15617299	12-Dec-24	17-Dec-24
15618894	12-Dec-24	19-Dec-24
15618894	12-Dec-24	12-Dec-24

The result for the above table should be as below for today ie 23-Dec-2024

Case Numbers not updated for the past 10 days

15606704

15611766

Request help on this

Thanks

Re: DAX expression to get records that have not been updated

SachinNandanwar — Mon, 23 Dec 2024 23:42:30 GMT

Not updated for past 10 days = CALCULATE(MAX(Case_Records[Case Number]),FILTER( Case_Records, NOT( Case_Records[Modified Date] >= TODAY() - 10 && Case_Records[Modified Date] <= TODAY() )))

Re: DAX expression to get records that have not been updated

rsrajkumar — Tue, 24 Dec 2024 00:36:05 GMT

Thanks SachinNandanwar . This was not working for me. It should not return 15618894 as that record was updated on 19-Dec-2024. It should return 15606704 as it was last modified on 11-Dec-2024

Re: DAX expression to get records that have not been updated

SachinNandanwar — Tue, 24 Dec 2024 01:34:30 GMT

Sorry I didnt realise that some case numbers will have update dates that fall both within and outside the 10-day interval.

Here is the calculated table approach

Final_Table= Var _tbl=SUMMARIZECOLUMNS(Case_Records[Case Number] , FILTER( Case_Records, ( Case_Records[Modified Date] >= TODAY() - 10 && Case_Records[Modified Date] <= TODAY() ))) RETURN FILTER( DISTINCT( SELECTCOLUMNS(Case_Records,Case_Records[Case Number])), NOT(Case_Records[Case Number] IN (SELECTCOLUMNS(_tbl, Case_Records[Case Number])) ))

Re: DAX expression to get records that have not been updated

rsrajkumar — Tue, 24 Dec 2024 03:37:35 GMT

Thanks SachinNandanwar . When there are multiple rows for the same Case Number and some of the Last Updated Dates are more than 10 days, and a few less than 10 days, which part of the DAX expression takes care of this? Am asking this because, my actual table has more records and some of them are not working

Re: DAX expression to get records that have not been updated

Kedar_Pande — Tue, 24 Dec 2024 06:03:16 GMT

rsrajkumar

Create a measure

CasesNotUpdated =
VAR LastModified =
CALCULATE(
MAX(Table[Modified Date]),
ALLEXCEPT(Table, Table[Case Number])
)
RETURN
IF(
DATEDIFF(LastModified, TODAY(), DAY) > 10,
1,
0
)

Use this measure in a visual (e.g., a table or slicer) to filter Case Numbers where the measure equals 1.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Re: DAX expression to get records that have not been updated

SachinNandanwar — Tue, 24 Dec 2024 06:42:42 GMT

Its the filter with the NOT IN clause takes care of it

FILTER( DISTINCT( SELECTCOLUMNS(Case_Records,Case_Records[Case Number])), NOT(Case_Records[Case Number] IN (SELECTCOLUMNS(_tbl, Case_Records[Case Number])) ))

Re: DAX expression to get records that have not been updated

rsrajkumar — Tue, 24 Dec 2024 13:26:00 GMT

Kedar_Pande Thanks a lot. It works as intended. I would like to count the total records for which the case has not been updated for more than 10 days. While applying a filter for 'CasesNotUpdated' works on the table, it does not allow applying to a Card.

Re: DAX expression to get records that have not been updated

rsrajkumar — Tue, 24 Dec 2024 13:29:19 GMT

Thanks a lot SachinNandanwar . I learnt something new from you on creating tables , and a few new functions!

Re: DAX expression to get records that have not been updated

Anonymous — Thu, 26 Dec 2024 02:36:42 GMT

Hi rsrajkumar ,

First of all thanks to Kedar_Pande for the quick reply and solution. I noticed that you also want to count the total number of records where the case has not been updated for more than 10 days, then you can create another measure.

(1) My test data is the same as yours.

(2) We can create a measure.

Count = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', [Case Number], "flag", [CasesNotUpdated] ), [flag] = 1 ) )

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: DAX expression to get records that have not been updated

rsrajkumar — Fri, 27 Dec 2024 01:13:51 GMT

Thanks Anonymous,Kedar_Pande , and SachinNandanwar . All the proposed solution by you works well!