Forum Discussion
Show value from other table
Dear friends
how can i display distinct of "Type" value from table 1 to Table visual that header from table2 (table1 and table2 relations as many-many )
Table1
| Date | Type | Site |
| 01-01-21 | AA | North |
| 01-01-21 | AB | North |
| 02-01-21 | BA | South |
| 02-01-21 | AA | North |
| 02-01-21 | BB | South |
Table2
| Date | Type | Site |
| 01-01-21 | AA | North |
| 01-01-21 | AB | North |
| 02-01-21 | BA | South |
| 02-01-21 | AA | North |
| 02-01-21 | BB | South |
| 02-01-21 | BC | South |
what i want to display on visual
| Site | Type (distinct count) |
| North | 2 |
| South | 3 |
but its always show as total distinct value (not seperated by "Site" row header)
| Site | Type (distinct count) |
| North | 5 |
| South | 5 |
thank in advance for any suggestion.
Hi Anonymous ，
You could create new table to union 2 tables, then create a measure to count "Site", the following formula to create :
（you mentioned that” table1 and table2 relations as many-many “，whether there is a relationship between the two tables will not affect the final result）
Step1: Create new table:
newtable = DISTINCT(UNION(SUMMARIZE('Table1',[Site],[Type]),SUMMARIZE('Table2',[Site],[Type])))Step2: New measure
discount = COUNTX('newtable','newtable'[Site])The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
Anonymous , Option are merged two tables and use them
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Or create a common tbale type an try this measure with that table
measure =
var _1 = union(summarize(Table1, table1[Site], table1[Type]), summarize(Table2, table2[Site], table2[Type])
var _2 = distinct(_1[Site])
return
countx(_1, [Site])- amitchandak
Anonymous , It should be from _2
return
countx(_2, [Site])
v-yalanwu-msftCommunity Support
Hi Anonymous ，
You could create new table to union 2 tables, then create a measure to count "Site", the following formula to create :
（you mentioned that” table1 and table2 relations as many-many “，whether there is a relationship between the two tables will not affect the final result）
Step1: Create new table:
newtable = DISTINCT(UNION(SUMMARIZE('Table1',[Site],[Type]),SUMMARIZE('Table2',[Site],[Type])))Step2: New measure
discount = COUNTX('newtable','newtable'[Site])The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
