topic Re: Count the occurrence of the value in 3 different tables in DAX Commands and Tips

Count the occurrence of the value in 3 different tables

Anonymous — Mon, 18 Nov 2024 12:31:20 GMT

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]))

Re: Count the occurrence of the value in 3 different tables

Kedar_Pande — Mon, 18 Nov 2024 12:45:40 GMT

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

Re: Count the occurrence of the value in 3 different tables

Anonymous — Mon, 18 Nov 2024 13:16:29 GMT

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!