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 All,
I have tried to simplify the data model with these two tables.
The first table is the Recognition Table with data as below
The second table is the Employee table as below:
The two tables are connected by a 1:* relationship between Employee ID and Recipient ID. One employee may receive multiple recognitions and it is possible that someone does not receive any. In this case, that employee will be only in the employee table
The visual I am working on is something like this. This is when there are no slicers selected
The dashboard has a number of slicers. For example, the Leader Name and Department Name are slicers. When nothing is selected, the expectation is to get all rows in the Employee table. In this case, 20.
When we select Leader 'ABC' in the slicer, the visual should change to something like below.
I am not able to get 4 as the # Employees when I use the slicer. I get total number of employees who have received a recognition but not the total number of employees based on the slicer selection.
Any suggestions on how to achieve this?
Solved! Go to Solution.
Hi @daxismyproblem ,
Here are the steps you can follow:
1. Create calculated table.
Table =
DISTINCT('Employee Table'[Leader Name])
2. Create measure.
of recongnitions =
var _select=SELECTEDVALUE('Table'[Leader Name])
var _column=SELECTCOLUMNS(FILTER(ALL('Employee Table'),'Employee Table'[Leader Name]=_select),"ID",[Leader ID])
var _count=
COUNTX(
FILTER(ALL('Reognition Table'),
'Reognition Table'[Category]=MAX('Reognition Table'[Category])&&'Reognition Table'[Learder ID] in _column),[Recipient ID])
return
IF(
_count=BLANK(),0,_count)
# Employees =
var _select=SELECTEDVALUE('Table'[Leader Name])
var _column=SELECTCOLUMNS(FILTER(ALL('Employee Table'),'Employee Table'[Leader Name]=_select),"ID",[Leader ID])
return
COUNTX(
FILTER(ALLSELECTED('Employee Table'),'Employee Table'[Leader ID] in _column),[Employee ID])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @daxismyproblem ,
Here are the steps you can follow:
1. Create calculated table.
Table =
DISTINCT('Employee Table'[Leader Name])
2. Create measure.
of recongnitions =
var _select=SELECTEDVALUE('Table'[Leader Name])
var _column=SELECTCOLUMNS(FILTER(ALL('Employee Table'),'Employee Table'[Leader Name]=_select),"ID",[Leader ID])
var _count=
COUNTX(
FILTER(ALL('Reognition Table'),
'Reognition Table'[Category]=MAX('Reognition Table'[Category])&&'Reognition Table'[Learder ID] in _column),[Recipient ID])
return
IF(
_count=BLANK(),0,_count)
# Employees =
var _select=SELECTEDVALUE('Table'[Leader Name])
var _column=SELECTCOLUMNS(FILTER(ALL('Employee Table'),'Employee Table'[Leader Name]=_select),"ID",[Leader ID])
return
COUNTX(
FILTER(ALLSELECTED('Employee Table'),'Employee Table'[Leader ID] in _column),[Employee ID])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
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 |
---|---|
106 | |
102 | |
77 | |
68 | |
61 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |