Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table that contains data about logins by employees. An employee can have multiple rows per day.
My report is filtering by day and I need to show in my report 3 cards. The first one should show the name of the employee with more logins, the second one the second employee with more logins, and in the third, the third employee with more logins.
I have been trying with ranks, but I'm not able to get a good result.
For instance, if I select day 1:
Card 1 should show C (9)
Card 2 should show A (7)
Card 3 should show D (2)
For instance, if I select day 4:
Card 1 should show D
Card 2 should show B
Card 3 should show A
Thanks in advance for your help.
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Ranking: =
RANKX ( ALL ( Employee[Employee] ), CALCULATE ( SUM ( Data[Logins] ) ),, DESC )
Rank one: =
VAR ranktable =
FILTER (
ADDCOLUMNS(
VALUES(Employee[Employee]),
"@rank", [Ranking:]
),
[@rank] = 1
)
RETURN
MAXX ( ranktable, Employee[Employee] ) & " ("
& CALCULATE (
SUM ( Data[Logins] ),
Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
) & ")"
Rank two: =
VAR ranktable =
FILTER (
ADDCOLUMNS(
VALUES(Employee[Employee]),
"@rank", [Ranking:]
),
[@rank] = 2
)
RETURN
MAXX ( ranktable, Employee[Employee] ) & " ("
& CALCULATE (
SUM ( Data[Logins] ),
Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
) & ")"
Rank three: =
VAR ranktable =
FILTER (
ADDCOLUMNS(
VALUES(Employee[Employee]),
"@rank", [Ranking:]
),
[@rank] = 3
)
RETURN
MAXX ( ranktable, Employee[Employee] ) & " ("
& CALCULATE (
SUM ( Data[Logins] ),
Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
) & ")"
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Ranking: =
RANKX ( ALL ( Employee[Employee] ), CALCULATE ( SUM ( Data[Logins] ) ),, DESC )
Rank one: =
VAR ranktable =
FILTER (
ADDCOLUMNS(
VALUES(Employee[Employee]),
"@rank", [Ranking:]
),
[@rank] = 1
)
RETURN
MAXX ( ranktable, Employee[Employee] ) & " ("
& CALCULATE (
SUM ( Data[Logins] ),
Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
) & ")"
Rank two: =
VAR ranktable =
FILTER (
ADDCOLUMNS(
VALUES(Employee[Employee]),
"@rank", [Ranking:]
),
[@rank] = 2
)
RETURN
MAXX ( ranktable, Employee[Employee] ) & " ("
& CALCULATE (
SUM ( Data[Logins] ),
Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
) & ")"
Rank three: =
VAR ranktable =
FILTER (
ADDCOLUMNS(
VALUES(Employee[Employee]),
"@rank", [Ranking:]
),
[@rank] = 3
)
RETURN
MAXX ( ranktable, Employee[Employee] ) & " ("
& CALCULATE (
SUM ( Data[Logins] ),
Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
) & ")"
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
I was struggling with the same issue, thanks @Jihwan_Kim . Your solution worked like a baby. But I managed to make it a single measure instead of three with below code. I had to create a table named "Rank" to acheive it though. "Rank" table had a single column "Ranks" with values from 1 to 3.
Rank one: =
VAR ranktable =
FILTER (
ADDCOLUMNS(
VALUES(Employee[Employee]),
"@rank", [Ranking:]
),
[@rank] = SELECTEDVALUE(Rank[Ranks])
)
RETURN
MAXX ( ranktable, Employee[Employee] ) & " ("
& CALCULATE (
SUM ( Data[Logins] ),
Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
) & ")"
It worked, thanks a lot!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |