cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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.

3 REPLIES 3
Solution Sage

hi @Anonymous Create a calculated column in the Devices table

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

hi @DimaMD ,

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

Anonymous
Not applicable

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])

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.