Forum Discussion
Solution SageTwo table common key and relationship issue...
Morning to all,
I have 2 tables...
Table 1
| Client ID- | Date------- | Key_1 |
| A | 01-01-22 | A | 01-01-22 |
| B | 01-05-22 | B | 01-05-22 |
| B | 01-06-22 | B | 01-06-22 |
| C | 01-16-22 | C | 01-16-22 |
| C | 01-17-22 | C | 01-17-22 |
| C | 01-18-22 | C | 01-18-22 |
Table 2
| Client ID- | Date------- | Key_2 |
| A | 01-01-22 | A | 01-01-22 |
| A | 01-03-22 | A | 01-03-22 |
| B | 01-06-22 | B | 01-06-22 |
| C | 01-16-22 | C | 01-16-22 |
| C | 01-17-22 | C | 01-17-22 |
Both tables are connected to Key column.
Issue is something like not all the dates from Table 1 is available in Table 2.
So when I put data in Table Visual, I got below...
| Client ID(from Table 1)- | Count(Table 1)- | Count(Table 2) |
| 1 | ||
| A | 1 | 1 |
| B | 2 | 1 |
| C | 3 | 2 |
Instead I want below visual...
| Client ID(from Table 1)- | Count(Table 1)- | Count(Table 2) |
| A | 1 | 2 |
| B | 2 | 1 |
| C | 3 | 2 |
I want count of A... 2, not 1 as shown above.
Concatenation of Client ID & Date is necessary for me.
I have a lot data and this is the simplest explaination to my problem.
If anyone have anything to share please,...
Hi ddpl ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create two measures as below:
Count(Table 1) = VAR _selclientid = SELECTEDVALUE ( 'Table1'[Client ID] ) VAR _count = CALCULATE ( COUNT ( 'Table1'[Date] ), FILTER ( ALLSELECTED ( 'Table1' ), 'Table1'[Client ID] = _selclientid ) ) RETURN _countCount(Table 2) = VAR _selclientid = SELECTEDVALUE ( 'Table1'[Client ID] ) VAR _count = CALCULATE ( COUNT ( 'Table2'[Date] ), FILTER ( ALLSELECTED ( 'Table2' ), 'Table2'[Client ID] = _selclientid ) ) RETURN _countBest Regards
3 Replies
Hi ddpl ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create two measures as below:
Count(Table 1) = VAR _selclientid = SELECTEDVALUE ( 'Table1'[Client ID] ) VAR _count = CALCULATE ( COUNT ( 'Table1'[Date] ), FILTER ( ALLSELECTED ( 'Table1' ), 'Table1'[Client ID] = _selclientid ) ) RETURN _countCount(Table 2) = VAR _selclientid = SELECTEDVALUE ( 'Table1'[Client ID] ) VAR _count = CALCULATE ( COUNT ( 'Table2'[Date] ), FILTER ( ALLSELECTED ( 'Table2' ), 'Table2'[Client ID] = _selclientid ) ) RETURN _countBest Regards
FreemanZSuper User
try to plot with the Client ID from table2?