topic Help counting values from one table to another table in DAX Commands and Tips

Help counting values from one table to another table

joseclaudio — Mon, 17 Jun 2024 18:30:56 GMT

Hello, need help with two columns in a table (non unique values) that need to be counted on another table with unique values

Table A:

Serial Number	EoL Date 2	EoV Date
FCH2048FT8P	Not Published yet	Not Published yet
FCH17018VD3	2024	Prior 2024
WZP20350SSA	Not Published yet	Not Published yet
FOC2133U12T	2026	2025
WZP20350SYP	Not Published yet	Not Published yet
FCH193093GN	Not Published yet	Not Published yet
FOC2010S2B1	2028	2027
FCH1504DVMX	Prior 2024	Prior 2024
FCW2018D098	2025	2025
FGL2019X04D	2024	2024
FJC2405M4JN	2027	2027
MXQ13800PF	Not Published yet	Not Published yet
FCH1346AK4P	Prior 2024	Prior 2024
FCW2336PQWG	2027	2027
FCH1508A1PV	Prior 2024	Prior 2024
FCW2132NBV0	2027	2027
WZP20350GCH	Not Published yet	Not Published yet
FCH193093CB	Not Published yet	Not Published yet
FCW2142NG24	2027	2027
FOC2016W1VK	2029	2029
FCH2127DQZV	Not Published yet	Not Published yet
FCW2336PRBL	2027	2027
WZP20350GDP	Not Published yet	Not Published yet
FCH2048FQ27	Not Published yet	Not Published yet
WZP20350T5M	Not Published yet	Not Published yet
FCH192981ZJ	Not Published yet	Not Published yet
FTX1923S4HT	2024	2024

Table B:

Date	Count of EoL	Count of EoV
Prior 2024
2024
2025
2026
2027
2028
2029
Not Published yet

A single serial number sometimes have the same value as EoL and EoV but there are cases when those values are different for the same serial number as shown:

FCH17018VD3

2024

Prior 2024

Been trying different ways but all the time the counts for both EoL and EoV always show the same value which is incorrect.

Re: Help counting values from one table to another table

Moetazzahran — Mon, 17 Jun 2024 22:59:10 GMT

Hello joseclaudio,

I created two measures Measure_EOL,Measure_EOV

Measure_EOL =

VAR currentselection=SELECTEDVALUE('Unique'[Unique Date])

VAR Tablecount=CALCULATE(COUNT('Table'[Serial Number]),'Table'[EoL Date 2]=currentselection,ALLEXCEPT('Table','Table'[EoL Date 2]))

RETURN

Tablecount

Measure_EOV =

VAR currentselection=SELECTEDVALUE('Unique'[Unique Date])

VAR Tablecount=CALCULATE(COUNT('Table'[Serial Number]),'Table'[EoV Date]=currentselection,ALLEXCEPT('Table','Table'[EoV Date]))

RETURN

Tablecount

To explain:
SELECTEDVALUE('Unique'[Unique Date]): Captures the currently selected date from the 'Unique' table.

CALCULATE: Calculates the count of 'Serial Number' based on the conditions provided.
ALLEXCEPT: Clears all filters on the 'Table' except those on the specified column ('EoL Date 2' or 'EoV Date').

If this serves the purspose and solves your requirement, please accept it as a solution and your kudo will be much appreciated.

Re: Help counting values from one table to another table

joseclaudio — Tue, 18 Jun 2024 17:16:18 GMT

It worked like a charm!!! thanks I've been struggling with these a couple of weeks and now you saved me.