how to compare 2 tables based on separate column values and distinct user access information

[rachelb123]

Hi, 

  I am creating my first real dashboard and I am trying to calculate the rate of unique users using a specific tool. I have two tables, one with usage data and another with user access data. The original way I calculated it only had one tool so it was pretty easy to just seperate out the users and pull their names from both tables and just compare but I'm not sure how to proceed when there is a dataflow with multiple tools in one table. 

Email	Name	Role	Group
abc@gmail.com	John Smith	Procurement Specialist	car
bcd@gmail.com	Smith John	Procurement Specialist	health
efg@gmail.com	Jane Doe	Procurement Specialist	health
hij@gmail.com	Doe Jane	Procurement Specialist	car
klm@gmail.com	Peter Spiet	Procurement Specialist	medicine
nop@gmail.com	Spiet Peter	Procurement Specialist	medicine
qrs@gmail.com	Don Jan	Procurement Specialist	health
tuv@gmail.com	Jan Don	Procurement Specialist	car

ToolName	UserName	UserEmail	UseDate
A	Doe Jane	n	1-Jul-25
A	Doe Jane	n	2-Jul-25
B	Smith John	n	1-Jul-25
C	Doe Jane	n	1-Jul-25
B	Smith John	n	2-Jul-25
A	Doe Jane	n	3-Jul-25
C	Peter Spiet	n	4-Jul-25
C	Spiet Peter	n	4-Jul-25
B	Don Jan	n	1-Jul-25

 Where Tool A = Group car users, Tool B = Group Health users, Tool C = Group Medicine users. 

I'm not sure how to associate these 2 columns iteratively for more tools down the line. Additionally to calculate rate of usage = Number of unique users/total number of users with access. I was hoping to get the below output.

Product: 

Tool	Adoption Rate
A	1/3
B	2/3
C	2/2

[Ashish_Mathur]

Hi,

In the result that you have shown, what is the significance of the first table.  Looks like the third table can be generated from the second one itself.  Why is the answer of C 2/2?  Also, why is Peter spelled in 2 ways?

[rachelb123]

Hi @Ashish_Mathur ,

  Peter Spiet and Spiet Peter are two seperate users for ease of copying it down, I just flipped the names. The first table is a table with all the users who have access to the main website and their groups determine which tool they have specific access to.  The second table is their daily usage data where you can see who has accessed which tool. Groups and Tools don't have the same name so Tool A = Group car users, Tool B = Group Health users, Tool C = Group Medicine users. C = 2/2 because only 2 users are in the group Medicine and they both accessed Tool C in the time period (of a quarter) so Adoption Rate = 2/2 = 1.0 I displayed it as 2/2 for ease of understanding formula my apologies. 

[Irwan]

hello @rachelb123 

please check if this accomodate your need.

create a new measure with following DAX.

Adoption Rate =
var _Distinct = 
CALCULATE(
    DISTINCTCOUNT('Tool'[UserName]),
    FILTER(
        'Tool',
        'Tool'[ToolName]=SELECTEDVALUE('Tool'[ToolName])
    )
)
var _Count = 
CALCULATE(
    COUNT('Tool'[UserName]),
    FILTER(
        'Tool',
        'Tool'[ToolName]=SELECTEDVALUE('Tool'[ToolName])
    )
)
Return
_Distinct&"/"&_Count

the result for C is 3/3 because Doe Jane, Peter Spiet, and Spiet Peter.

Peter Spite and Spiete Peter are counted as different value.

Hope this will help.
Thank you.

[rachelb123]

@Irwan , 

  I did look over your file, it does not take user access into account. It is just counting against the lines present in the second table. I created a new table which looks like this to make it easier - 

Email	Name	Role	Group
abc@gmail.com	John Smith	Procurement Specialist	A
bcd@gmail.com	Smith John	Procurement Specialist	B
efg@gmail.com	Jane Doe	Procurement Specialist	B
hij@gmail.com	Doe Jane	Procurement Specialist	A
klm@gmail.com	Peter Spiet	Procurement Specialist	C
nop@gmail.com	Spiet Peter	Procurement Specialist	C
qrs@gmail.com	Don Jan	Procurement Specialist	B
tuv@gmail.com	Jan Don	Procurement Specialist	A

[Irwan]

hello @rachelb123 

got it, thats why 2/2 on C.

please check if this accomodate your need.

create three new measures with following DAX

DistinctCount = DISTINCTCOUNT('Tool'[UserName])

Count = CALCULATE(COUNT('Tool'[UserName]),ALL('Tool'))

Adoption Rate =
var _DistinctA = COUNTX(FILTER('Email','Email'[Group]="Car"),[DistinctCount])
var _CountA = COUNTX(FILTER('Email','Email'[Group]="Car"),[Count])
var _DistinctB = COUNTX(FILTER('Email','Email'[Group]="Health"),[DistinctCount])
var _CountB = COUNTX(FILTER('Email','Email'[Group]="Health"),[Count])
var _DistinctC = COUNTX(FILTER('Email','Email'[Group]="Medicine"),[DistinctCount])
var _CountC = COUNTX(FILTER('Email','Email'[Group]="Medicine"),[Count])
Return
IF(
    SELECTEDVALUE('Tool'[ToolName])="A",
    _DistinctA&"/"&_CountA,
IF(
    SELECTEDVALUE('Tool'[ToolName])="B",
    _DistinctB&"/"&_CountB,
IF(
    SELECTEDVALUE('Tool'[ToolName])="C",
    _DistinctC&"/"&_CountC
)))

create a relationship between those two table.

Here is the result

else you can use merge queries to combine those two table so you can achive the result without relationship.

Hope this will help.
Thank you.

[rachelb123]

Thank you @Irwan, 

  This unfortunately did not work. I believe its because my example table was too restrictive in comparison to my real data. In my real data Tool C is used by all groups (Car, Medicine and Health) I created this table to provide a more realistic representation of my data. 

Email	Name	Role	Tool
abc@gmail.com	John Smith	Procurement Specialist	A
bcd@gmail.com	Smith John	Procurement Specialist	B
efg@gmail.com	Jane Doe	Procurement Specialist	B
hij@gmail.com	Doe Jane	Procurement Specialist	A
klm@gmail.com	Peter Spiet	Procurement Specialist	C
nop@gmail.com	Spiet Peter	Procurement Specialist	C
qrs@gmail.com	Don Jan	Procurement Specialist	B
tuv@gmail.com	Jan Don	Procurement Specialist	A
abc@gmail.com	John Smith	Procurement Specialist	C
bcd@gmail.com	Smith John	Procurement Specialist	C
efg@gmail.com	Jane Doe	Procurement Specialist	C
hij@gmail.com	Doe Jane	Procurement Specialist	C
efg@gmail.com	Jane Doe	Procurement Specialist	C
hij@gmail.com	Doe Jane	Procurement Specialist	C

 This is more realistic to the data I have. I went in and changed all the grouping in my table to make it easier for me to calculate so now Tool is present next to each user but I did have to duplicate the user names as some users have access to multiple tools. Please let me know if this is not a good form of practice, I'm new to Power BI so im not really sure about these things. Thank you in advance. I do want to add that this user access table will be pretty static through out the year. 

[Irwan]

hello @rachelb123 

absolutely you can.

in my opinion, calculate Tool in Email tabl is a good step to achive your goal.

this has similar result when using merge queries combining two table into one which makes the distinct and count calculation much easier.

for duplicate value, you need to define another condition to differentiate between those duplicates.

Thank you.

[rachelb123]

Hi @Irwan,

   So it should be 2/2 because Doe Jane is not in the group Medicine so her presence in the that tool wouldn't be counted towards adoption rate, I hope that made sense. This is what I have been struggling with, how to connect it based on group to tool association. Thanks