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 have a large Table visual with fields from 4 different data source tables. I want to show a 0 in the places where there are blanks now. I have seen other solutions but none that are related to having multiple data source tables.
Here is a sample of the data fields I am using and Table visual:
Table 1
Parent Account | Child Account | Start Date | State |
Alliance | Central Rehab | 1/1/2020 | NY |
Invigorate | Trace Health | 2/1/2018 | FL |
Preferred | Grace Point H & R | 3/8/2011 | CA |
Table 2
Parent Account | Child Account | June Rate | July Rate |
Alliance | Central Rehab | 20 | 53 |
Invigorate | Trace Rehab | 3 | 67 |
Table 3
Parent Account | Child Account | June Score | July Score |
Invigorate | Trace Rehab | 100 | 98 |
Preferred | Grace Point H & R | 25 | 75 |
Table 4
Parent Account | Child Account | June Patient Count | July Patient Count |
Alliance | Central Rehab | 254 | 189 |
Invigorate | Trace Rehab |
| 25 |
Preferred | Grace Point H & R | 80 | 101 |
Solved! Go to Solution.
Hi @aafflick ,
Situation 1: Your June Rate, July Rate, June Sorce, etc. are all measures.
You can refer to this post.
1.Create a new table named Table(5) with the child account column and the parent account column. The two columns of data must contain all of the four tables.
2.Create relationships between the four tables and the new table, and then the parent account column and child account column in the visual are selected from the new table. Make sure add +0 to the calculation result of measures.In this way, the blanks on the visual will be replaced with 0.
Situation 2: Your June Rate, July Rate, June Sorce, etc. are all values from the data source.
1.Select Merge Queries as New.
2.Both select the Parent Account column and click OK.
3.Expand the columns you need and don't select "Use original column name as prefix".
4.Table and Table(2) are merged.
5. Repeat the operation just now to merge the Merge1 table with Table (3) and Table (4), and get the following.
6.Use Replace Values to replace null with 0.(Remember to select the column where the null is located before performing the replacement operation.)
7.Now the blanks in the view are replaced with 0
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @aafflick ,
Situation 1: Your June Rate, July Rate, June Sorce, etc. are all measures.
You can refer to this post.
1.Create a new table named Table(5) with the child account column and the parent account column. The two columns of data must contain all of the four tables.
2.Create relationships between the four tables and the new table, and then the parent account column and child account column in the visual are selected from the new table. Make sure add +0 to the calculation result of measures.In this way, the blanks on the visual will be replaced with 0.
Situation 2: Your June Rate, July Rate, June Sorce, etc. are all values from the data source.
1.Select Merge Queries as New.
2.Both select the Parent Account column and click OK.
3.Expand the columns you need and don't select "Use original column name as prefix".
4.Table and Table(2) are merged.
5. Repeat the operation just now to merge the Merge1 table with Table (3) and Table (4), and get the following.
6.Use Replace Values to replace null with 0.(Remember to select the column where the null is located before performing the replacement operation.)
7.Now the blanks in the view are replaced with 0
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Are you using measures or calculated columns for the rate/count or is that data from the source? If a measure, make sure it's data type is a number and include a +0 the end of the calculation.
Otherwise I would consider adding a Replace Value step in Power Query that replaces the blank values with a 0.
Table Visual Results
Parent Account | Child Account | Start Date | State | June Rate | July Rate | June Score | July Score | June Patient Count | July Patient Count |
Alliance | Central Rehab | 1/1/2020 | NY | 20 | 53 |
|
| 254 | 189 |
Invigorate | Trace Health | 2/1/2018 | FL | 3 | 67 | 100 | 98 |
| 25 |
Preferred | Grace Point H & | 3/8/2011 | CA |
|
| 25 | 75 | 80 | 101 |
Desired Table Visual Results
Parent Account | Child Account | Start Date | State | June Rate | July Rate | June Score | July Score | June Patient Count | July Patient Count |
Alliance | Central Rehab | 1/1/2020 | NY | 20 | 53 | 0 | 0 | 254 | 189 |
Invigorate | Trace Health | 2/1/2018 | FL | 3 | 67 | 100 | 98 | 0 | 25 |
Preferred | Grace Point H & | 3/8/2011 | CA | 0 | 0 | 25 | 75 | 80 | 101 |
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |