Data model mapping issue

[Anonymous]

Hi all,

i have two table 

Requirement Table:

Department ID	No of Employee Required
D1	5
D2	5
D3	5
D4	4
D5	6

Result Table:

Date	Employee ID	Result	Department ID
5/5/2019	E1	Selected	D1
5/5/2019	E2	Selected	D1
5/5/2019	E3	Selected	D2
5/5/2019	E4	Selected	D3
5/5/2019	E5	Selected	D3
5/6/2019	E6	Selected	D4
5/6/2019	E7	Selected	D5
5/6/2019	E8	Selected	D2
5/6/2019	E9	Selected	D3
5/6/2019	E10	Selected	D3

I have mapped these two tables by Department ID

I have a Date filter

when I select Date: 5/5/2019

it shows Result like

Department ID	No of Employee Required	No of Employee Selected
D1	5	2
D2	5	1
D3	5	2

but I want to show all requirement Details 

Expected Result

Department ID	No of Employee Required	No of Employee Selected
D1	5	2
D2	5	1
D3	5	2
D4	4	0
D5	6	0

[v-xicai · Accepted solution]

Hi  @Anonymous ,

Based on the Cross filter direction of the relationship between the two table as Single instead of Both, you can create measure like DAX below, then tick on the " Show items with no data " of Department ID when display result in table visual .

No of Employee Selected = CALCULATE(COUNT(Result[Employee ID]),FILTER(ALLSELECTED(Result),Result[Department ID]=MAX(Requirement[Department ID])))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, v-xicai

is it possible to show the total value? 

[Anonymous]

Hi v-xicai,

Thanks for your response.

In Result table, Result column I have many categorizations(Rejected, Selected). Particularly I want to get 'selected' count only.

Result table

Date	Employee ID	Result	Department ID
05-05-2019	E1	Selected	D1
05-05-2019	E2	Selected	D1
05-05-2019	E3	Rejected	D2
05-05-2019	E4	Rejected	D3
05-05-2019	E5	Selected	D3
05-06-2019	E6	Selected	D4
05-06-2019	E7	Selected	D5
05-06-2019	E8	Selected	D2
05-06-2019	E9	Selected	D3
05-06-2019	E10	Selected	D3

how to  alter this dax query 

No of Employee Selected = CALCULATE(COUNT(Result[Employee ID]),FILTER(ALLSELECTED(Result),Result[Department ID]=MAX(Requirement[Department ID])))

Thanks in Advance