topic Re: Measure to map Unique IDs Added and reduced to list in DAX Commands and Tips

Measure to map Unique IDs Added and reduced to list

Anonymous — Sat, 04 Jul 2020 22:52:06 GMT

Hi All,

I'm working on a set of two tables, active users of a tool on April and May. I want to calculate the number of users who were added on the tool and those who stopped using it (i.e. left the org) based on their unique id.

How do i build a measure/s that helps calculate the additions and reductions to the list?

For eg. a measure that would show 1 for added for freddie.m and a measure that should 1 reducded i.e. ked.kal.

I will be build a matrix report comparing such metrics for each month.

April (Table)	May (Table)

Unique Id	Unique Id
s.ray	s.ray
bill.first	bill.first
ked.kal	freddie.m
j.lenny	j.lenny

Thanks for your time!

Re: Measure to map Unique IDs Added and reduced to list

lbendlin — Sat, 04 Jul 2020 23:42:43 GMT

Have a look at this thread, it covers a similar scenario

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Return-new-removed-values-within-a-data-set-compared-to-their/td-p/1198939

tldr; : use the EXCEPT() function both ways.

Re: Measure to map Unique IDs Added and reduced to list

Anonymous — Sun, 05 Jul 2020 00:02:26 GMT

lbendlin Thank you so much.

I will take the time to understand that solution.

Is it possible to have an easier workaround?

For example

1. Have a measure that counts unique IDs common between April and May

2. To find unique ids lost = Total count of April - Count of unique IDs common for April and May

3. To find unique ids added = Total Count of May - Count of Unique IDs common for April and May

Would this potentially work?

Re: Measure to map Unique IDs Added and reduced to list

lbendlin — Sun, 05 Jul 2020 01:13:43 GMT

That's basically what EXCEPT() does. All the other lines are just preparation.