Forum Discussion
One to Many Relation Ship Issue
Hello Everyone,
I have a table like the image below
Have a connection like this
My expected output is
but I'm getting below result
How can I achieve this result by using the same data model?
----------------------------------------------------------------------------------------------
Dim Table Data
ID Name
| ID1 | XXX |
| ID2 | YYY |
| ID3 | XXX |
Fact Table Data
ID Project
| ID1 | A |
| ID1 | B |
| ID1 | C |
| ID1 | D |
| ID2 | E |
| ID2 | F |
| ID2 | G |
Hi vengadesh_p
please follow below steps to get the output, please see below pbix file for your reference
https://drive.google.com/file/d/16sf3HBGwfC7fN53bkeanKhMZEGN8xvjK/view?usp=drive_link
1. create relationhip between two tables using id column
2. use drop down menu on name column and use count distinct as shown below
3. use the dropdown menu of id column and select show items with no data as shown below
If there are blank values in visuals, they will be hided by default. It is by design for Power BI. So, after creating relationships, you need to turn on the option "Show items with no data".
5 Replies
Hi vengadesh_p
please follow below steps to get the output, please see below pbix file for your reference
https://drive.google.com/file/d/16sf3HBGwfC7fN53bkeanKhMZEGN8xvjK/view?usp=drive_link
1. create relationhip between two tables using id column
2. use drop down menu on name column and use count distinct as shown below
3. use the dropdown menu of id column and select show items with no data as shown below
If there are blank values in visuals, they will be hided by default. It is by design for Power BI. So, after creating relationships, you need to turn on the option "Show items with no data".
Hi! vengadesh_p
Try to remove the relationship and create it again. I see the data as you are expecting.
I need to show ID3 with a blank value, also I want to show #Name as Measure
Hi vengadesh_p ,
You need to change the relationship to both:
Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
1. Make relationship bi-directional
2. Use ID from Dim Table in your matrix
3. Right click on ID, and select "Show items with no data"
4. Measure:
# Name = DISTINCTCOUNT('Dim Table'[Name])This is my outcome:
