Two table common key and relationship issue...

[ddpl]

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,...

[ddpl · Accepted solution]

@FreemanZ ,

Can't do it because Table 1 is the main table which has almost all the data and dates except few of them shown above.

So I have to put Table 1 column first.

I have changed a bit in question, please check again.

[Anonymous · Accepted solution]

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
    _count

Count(Table 2) = 
VAR _selclientid =
    SELECTEDVALUE ( 'Table1'[Client ID] )
VAR _count =
    CALCULATE (
        COUNT ( 'Table2'[Date] ),
        FILTER ( ALLSELECTED ( 'Table2' ), 'Table2'[Client ID] = _selclientid )
    )
RETURN
    _count

Best Regards

[FreemanZ]

try to plot with the Client ID from table2?

[ddpl · Accepted solution]

@FreemanZ ,

Can't do it because Table 1 is the main table which has almost all the data and dates except few of them shown above.

So I have to put Table 1 column first.

I have changed a bit in question, please check again.