One-to-one wrong filter

[Grieclolu]

Hi everyone,

I just uploaded 2 tables:

- the first is related to the company staff ( all the staff with personal ID, for each month with a specific column)
- the second is related to the terminations, stil with personal ID and month

For each of them, i also perfomed a new coulum, where there is a unique code formed by the person id + the month.

With these tables, i settled a one-to-one relationship. 

The problem is that this relationship does not work properly, for example: if i want to see how many terminations (information present in the second table) there have been per gender (information present in the first table), PBI is not able to filter it (you cna see it in the screen below).

How can i handle/fix it?

Thanks a lot,

Luca

[Greg_Deckler]

@Grieclolu Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Grieclolu]

Dear @Greg_Deckler ,

Thank you for your answer. I try to explain better through these two tables. 
I uploaded these two tables is PBI and i linked them throuhg a 1-1 relation (ID).

In the report, i would like to see how many terminations i have (second table) per gender, but i doid't get the expected result (as you can see in the screen).

Thanks in advance for your help.

Luca

Gender	ID
Female	1_1
Female	10_1
Female	1000_1
Female	10023_1
Male	1004_1
Female	10045_1
Male	10061_1
Female	10072_1
Male	10089_1
Male	10093_1
Female	10098_1
Female	10110_1
Female	10118_1
Female	10126_1
Female	10128_1
Male	10137_1
Female	10138_1
Female	10140_1
Female	1015_1

Cluster	ID
Voluntary	1008_1
Involuntary	10089_2
Voluntary	10092_1
End of contract	10118_2
Voluntary	10137_6
Voluntary	10165_7
Voluntary	10239_8
Involuntary	10244_8
Voluntary	1030_4
Voluntary	10332_3
Involuntary	10360_3
Voluntary	10389_4
Voluntary	1040_2
Voluntary	10421_3
Involuntary	10445_1

[Greg_Deckler]

@Grieclolu OK, so you are getting blanks (unmatched employees) in your visual and you don't want that. So, try creating these measures and put them in your Values instead of Count of ID from you second table. 

Male Terminations =
  COUNTROWS(
    FILTER(
      'GenderTable',
      [Gender] = "Male" &&
      [ID] IN SELECTCOLUMNS('Terminations',"__ID",[ID])
    )
  )



Female Terminations =
  COUNTROWS(
    FILTER(
      'GenderTable',
      [Gender] = "Female" &&
      [ID] IN SELECTCOLUMNS('Terminations',"__ID",[ID])
    )
  )

[Grieclolu]

@Greg_Deckler thanks for your answer, but i would like to automaticall get the result. Of cours the the data i posted are just an example, but the databes is much more big. So, i would like to have a relation that work automatically, since i have an ID for each empoyee and month. So why i can't get the desired result? I'im doing something wrong? Tell me if you need some other data to help me.

Thank you a lot again 🙂

[Greg_Deckler]

@Grieclolu Because you have a bunch of employees that aren't terminated and thus if you count the rows in the terminations table, there are a bunch of unmatched rows, which results in the legend category of "blank". If you write those two measures, they will always work and you don't have to do anything additional to keep the data up-to-date.

[Grieclolu]

@Greg_Deckler I understand. Is there any other way ot automatic match the two tables? for example using other type of relation? The problem is that the data base is very big, so you should do this operation for many other cases, for example, age, type of contract, nationality etc. Moreover, i would like to add other tables, with other information, so it will became very difficult to manage everythingh with this measure. 
Maybe you can advise me, as i said, another solution in how to put toghether date, or table, or relations.

Thanks a lot again,

Luca

[Greg_Deckler]

@Grieclolu Use the Filters pane and filter your legend to not include (Blank)?

[Grieclolu]

It could't be a solution, since almost all the rows are red as blank.
In the screen below, i have 834 rows, that means 834 terminations. If i not include the blank, i will lose almost all the information (824). Do you need some example more? Or maybe the pbix file?

[Greg_Deckler]

@Grieclolu So you don't have those terminated employees in your Gender table? Sorry, confused. PBIX would definitely help. See mine attached under my signature.

[Grieclolu]

@Greg_Deckler I saw your attached, and my file is the same as yours. But it is wrong too. In fact, among the 15 employees present in the "table", you have 13 blanks, and only two correct values (1 male 1 female). I'd like to have all the 15 values filtered as male or female.
I don't know if you understood my request. Basically the result i want is just have all the values correctly filtered.

[Greg_Deckler]

@Grieclolu I understand but if all of your terminated employees are in your "Gender" table then they would all be counted and if your terminated employees are NOT in your "Gender" table, then how in the world do you know their Gender to begin with? See the dilemma here?

[Grieclolu]

@Greg_Deckler  I belive is correct that all the terminated are present in the gender table.
Let's make another example:
i have 4 emoloyees in Table 1, A, B, C, D; A and B are male, while C and D female.
then i have 2 employees in table 2 (terminations), A and D. 

My expectation is that, by creating the relationship, A and D could automaically be clustered as male (A) and female (B).

Can you do something like this?

Many thanks 🙂

[Greg_Deckler]

@Grieclolu So I implemented your scenario and it 100% works. See attached PBIX, Gender2, Table2, Page 2.

[Grieclolu]

@Greg_Deckler Perfect, in this example it works, so you see the result i want. Now, how can fix the previous example? Or any other bigger dataset?

Many thanks

[Greg_Deckler]

@Grieclolu See attached PBIX. I added user E to terminations list and you can see that since there are no matching records for E in Gender2 table, you get a blank in your donut chart. My suspicion is that you don't actually have all of the terminated employees in your Gender table or that you have trailing spaces or something in your data that is causing the records to be unmatched between the two tables.

[Grieclolu]

@Greg_Deckler thanks for your efforts and suggestions, but i don't thing the problem is linked to unmethed date. I double checked it, and you also have my two tables i shared with you (you also use in pbix), so you can chack by yourself. 
Do you have any other suggestion?

Thanks a lot, Luca

[Greg_Deckler · Accepted solution]

@Grieclolu Right, I used your 2 tables originally, not a single employee in Gender that matches with Terminations. Check it yourself in the updated PBIX attached below signature. Gender3, Table3, Page3.