Count the occurrence of the value in 3 different tables

[Anonymous]

Hi! 
I need to find how many times unique ID occures in table B and C and count how many times this Unique ID appears in table A.
In a card visual I need to display this value. So far I was trying below measure, and couple of different once, but nothhing works. It filters it correctly, but it does not show me the proper value in the Card visual. 

Measure =

var _a = SELECTEDVALUE('table A'[Unique_ID])

var _b = COUNTROWS(

    FILTER('Table B', 'Table B'[Unique_ID] = _a))

var _c = COUNTROWS(

    FILTER('Table C', 'Table Cv'[Unique_ID] = _a))

var _d = _b + _c

RETURN

IF(_d > 0, DISTINCTCOUNT('TableA'[Unique_ID]))

[Kedar_Pande · Accepted solution]

@Anonymous 

In a card visual, there is no row context to filter the tables, so SELECTEDVALUE or similar row-based filtering won't work properly.

Correct Measure:

Measure =
VAR TableBIDs = DISTINCT('Table B'[Unique_ID])
VAR TableCIDs = DISTINCT('Table C'[Unique_ID])
VAR CombinedIDs = UNION(TableBIDs, TableCIDs)
VAR IDsInA = FILTER(CombinedIDs, NOT(ISBLANK(LOOKUPVALUE('Table A'[Unique_ID], 'Table A'[Unique_ID], [Unique_ID]))))
RETURN
COUNTROWS(IDsInA)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous]

Hi Kedar, 
Thank you, that works ! The only thing which I changed is below, as I wanted to only count distinct values from both columns B and C. 
 

VAR CombinedIDs = DISTINCT(UNION(TableBIDs, TableCIDs))  

 

Thank you!