topic DAX function to count values not duplicated in DAX Commands and Tips

DAX function to count values not duplicated

marquesj — Thu, 07 Mar 2024 11:01:40 GMT

Hi all,

I am running a report for a wharehouse to check what is inside, what was removed and what has entered.

I want to focus on what was removed from one day to another and exclude what entered. Below there's the structure of the table:

Date // Package // Dwell

03/05/2024	1	2
03/05/2024	2	2
03/05/2024	3	2
03/05/2024	4	5
03/05/2024	5	5
03/06/2024	1	3
03/06/2024	2	3
03/06/2024	3	3
03/06/2024	6	0
03/06/2024	7	0

On March 5th there were 5 packages on the wharehouse and on March 6th there were also 5 packages, but packages 4 and 5 were removed and packages 6 and 7 have entered. The final result I want to find is 3 (number of packages that remained from one day to another removing the packages with Dwell=0 because they're new)

Thank you!

Re: DAX function to count values not duplicated

lbendlin — Thu, 07 Mar 2024 19:28:21 GMT

COUNTROWS(INTERSECT())

Re: DAX function to count values not duplicated

Anonymous — Fri, 08 Mar 2024 06:32:03 GMT

Hi marquesj ,

1. Create a calculated column to get the data retained from the previous day.

Column = VAR _pre = CALCULATE(COUNT('Table'[Package]),FILTER(ALL('Table'),'Table'[Date Dwell] = EARLIER('Table'[Date Dwell]) - 1 && 'Table'[Package ] = EARLIER('Table'[Package ]))) RETURN _pre

2. Create a calculated column to get the count of retained packages from the previous day.

Column 2 = VAR _cou = CALCULATE(SUM('Table'[Column]),FILTER(ALL('Table'), 'Table'[Column] <> BLANK() &&'Table'[Date Dwell] = EARLIER('Table'[ Date Dwell]))) RETURN IF('Table'[Column] <> BLANK(),_cou,BLANK())

3. If you want to view the retained data, you can filter out the data whose calculated column is not empty in the filters pane, as shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: DAX function to count values not duplicated

marquesj — Fri, 08 Mar 2024 12:20:40 GMT

Hi Clara,

Thank you so much for helping out. Reading again my topic, I have made some confusion. The result I'm looking for is 2, as packages 4 and 5 were removed and therefore don't duplicate from one day to another.

Using this way I'll be given the opposite result that I need. The result I'm looking for is 2 as packages 4 and 5 were the ones that were removed:

Thank you

Re: DAX function to count values not duplicated

lbendlin — Fri, 08 Mar 2024 14:53:20 GMT

COUNTROWS()-COUNTROWS(EXCEPT())

Re: DAX function to count values not duplicated

Anonymous — Mon, 11 Mar 2024 08:09:47 GMT

Hi marquesj ,

1. The expression of column 2 can be modified as:

Column 2 = IF('Table'[Column] = BLANK(),1,BLANK())

2. Create a calculated column to count new values.

Column 3 = VAR _cou = CALCULATE(SUM('Table'[Column 2]),FILTER(ALL('Table'), 'Table'[Column 2] <> BLANK()&&'Table'[Date Dwell] = EARLIER('Table '[Date Dwell]) )) RETURN IF('Table'[Column 2] <> BLANK(),_cou,BLANK())

3. The final result is shown in the figure below. All data are displayed on March 5 because there is no previous date for comparison, so all data are considered new.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: DAX function to count values not duplicated

marquesj — Mon, 11 Mar 2024 11:57:45 GMT

HI Anonymous

It seems that isn't working. Dwell days are not contemplated and it's counting only the unique packages of day 6/03. If I manually put the filter on the visual for "Dwell = 0" I'm left with very few results which don't match the reality