Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I want to create a visual that is using data from 3 related tables but encountered some mismatch issue. The sample tables look like this:
Table A
Item Number | Volume |
a | 10 |
a | 5 |
b | 11 |
null | 7 |
Table B (no null value in this table)
Item Number | Category Number |
a | 001 |
b | 002 |
Table C (no null value in this table)
Catogory Number | Catogory Name |
001 | Grocery |
002 | Kitchen |
As I want to create a pie chart presenting the volume distribution by item's Catogory Name, I got some 'blank' because there is 'null' value in the first table (as below).
Known I cannot combine the existing 3 tables into 1, and now I want to change the 'blank' in my pie chart to 'unknown'. Since the 'blank' here is not a value but a representation of mismatch. How can I achive this? Thanks!
My attempts:
1. change the null value in Table A to unknown -- fail
2. append a row with null value to both Table B and C, and try to link "null" with "null" -- still not working
Solved! Go to Solution.
@hpilearn you need to have something that will represent the values in table A also in table B (and in this case also table C cause they are in 1:1 so they actually should be 1 table). If you replace the null with unkonwn you also need to have the same unknown text in table B also with unknown text for category number and also in table C unkonwn text on the values of the new row you need to add.
Better to join table B and C before you bring it to the model, then you will need to do that only on one table.
@hpilearn the blank appears when you have records in the many side of a relationship that doesn't have a matching key in the table at the one side. You can't replace it. You should always strive to have a model that each row in the many side has a represntative in the one side. Can you share a screenshot of the model view and write what columns play in the relationship.
Hi, thank you very much for the help! The relationship look like this:
There are null value in table A. Table B&C don't have any null value.
@hpilearn you need to have something that will represent the values in table A also in table B (and in this case also table C cause they are in 1:1 so they actually should be 1 table). If you replace the null with unkonwn you also need to have the same unknown text in table B also with unknown text for category number and also in table C unkonwn text on the values of the new row you need to add.
Better to join table B and C before you bring it to the model, then you will need to do that only on one table.
Hi,
To achieve this in simple way, you can uncheck the blank on the visual filter.
Thanks
Hari
Hi Hari, thank you for your reply. I'm tyring to replace the "blank" with the text "unknown" but not to hide it. Do you have any suggestion on that?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |