Unique Count : Between Multi Tables

[Chandrashekar]

Hello,

Could you please help to get DAX formula to get Unique count between two tables.

PBIX: PBIX Sample 

Formula: 

Measure2 = CALCULATE(COUNTROWS(DISTINCT(Table2[Number2])),treatas('Table1',Table1[Number],Table2[Number2]))

Regards,

Chandrashekar B

[Chandrashekar]

Hello,

Could you please help to get DAX formula to get Unique count between two tables.

PBIX: PBIX Sample 

Formula: 

Measure2 = CALCULATE(COUNTROWS(DISTINCT(Table2[Number2])),treatas('Table1',Table1[Number],Table2[Number2]))

Answer: Should be 5 as A4 is missing in Table2

Regards,

Chandrashekar B

[tamerj1]

Hi Chandra,

What exactly are you trying to count?

[Chandrashekar]

Hello,

am trying to count Number ticket(Table1) present in Table2.

Kindly note Table2 may have Number which is not present in Table1.

Regards,

Chandrashekar B

[tamerj1 · Accepted solution]

@Chandrashekar 
You can use 

Count Available in Both = 
COUNTROWS ( 
    FILTER ( 
        INTERSECT ( 
            VALUES ( Table1[Number] ), 
            VALUES ( Table2[Number2] ) 
        ), 
        NOT ISBLANK ( [Number] ) 
    ) 
)

[Chandrashekar]

Hello,

Thank you. It is working fine.

Regards,

Chandrashekar B