Forum Discussion

marquesj's avatar
marquesj
Regular Visitor
2 years ago

DAX function to count values not duplicated

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 en...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

marquesj's avatar
marquesj
Regular Visitor
2 years ago

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

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    COUNTROWS()-COUNTROWS(EXCEPT())