Count of rows based on slicer selection but ignoring relationship

[daxismyproblem]

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?

[Anonymous · Accepted 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

[lbendlin]

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...