Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Grieclolu
Regular Visitor

One-to-one wrong filter

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

 

Grieclolu_0-1664746667820.png

 

 

 

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

17 REPLIES 17
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

Grieclolu_0-1664799105189.png

 

 

GenderID
Female1_1
Female10_1
Female1000_1
Female10023_1
Male1004_1
Female10045_1
Male10061_1
Female10072_1
Male10089_1
Male10093_1
Female10098_1
Female10110_1
Female10118_1
Female10126_1
Female10128_1
Male10137_1
Female10138_1
Female10140_1
Female1015_1

 

ClusterID
Voluntary1008_1
Involuntary10089_2
Voluntary10092_1
End of contract10118_2
Voluntary10137_6
Voluntary10165_7
Voluntary10239_8
Involuntary10244_8
Voluntary1030_4
Voluntary10332_3
Involuntary10360_3
Voluntary10389_4
Voluntary1040_2
Voluntary10421_3
Involuntary10445_1

@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])
    )
  )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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 🙂

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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?

 

Grieclolu_0-1664869818483.png

 

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

@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?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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 🙂

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

Greg_Deckler_0-1664877144591.png

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.