How to create a calculated column in one table based on conditions from another table

[Anonymous]

Hi All,

I am looking for an answer to the below question I have while preparing some visuals in Power BI. Appreciate your help and valuable advice on this query.

I have data in a table (Users table) as below:

I need to find out the most frequent user based on the duration they have logged into the device. In this table, the most frequent user for the deviceId=1, would be User C. Similarly, the most frequent user for the deviceId=2, would be User Y.

I have another existing table (Devices table) as below and want to add a new column 'mostFrequentUser' (yellow highlighted) to this table which will be the most frequent user for the corresponding devices based on calculations from the Users table.

A solution I found out for this problem is to create a measure variable (Max User) in the the Users table as below. 

Difference = DATEDIFF(Users[firstSeen],Users[lastSeen],MINUTE)

Max user =
CALCULATE (
MAX (Users[User] ),
FILTER (
ALL ( 'Users' ),
[Difference] = MAX ( 'Users'[Difference])
&& [deviceID] = SELECTEDVALUE ( Users[deviceID] )
)
)

But, I need to filter some visuals based on the most frequent user and the end user of the report is demanding to add a slicer to filter using the most frequent user in the report.  Since, we can not use a measure variable as a slicer, the above measure variable is not doing the job and I would need the above logic to be implemented as a new colum in the 'Devices' table, so that I can add a slicer in the report for the most frequent users.

Could anyone please help me with this.

Thanks in advance for your help.

[DimaMD]

hi @Anonymous Create a calculated column in the Devices table

Max user = 
CALCULATE (
MAX ('UsersTable'[User] ),
FILTER (
'UsersTable',
[Difference] = 'UsersTable'[Difference]
&& [deviceID] =  DevicesTable[deviceID]
)
)

[Anonymous]

hi @DimaMD ,

Unfortunately, it didn't return the correct values as expected.

[Anonymous]

Hi @DimaMD ,

To be more specific:

The problem I am facing with this solution is associated with MAX function. 

MAX('Users'[accountName]) is returing the alphabetically higher value instead of the most frequent user.

MAX('Users'[accountName])