Forum Discussion
Solved
Help counting values from one table to another table
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.
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]))RETURNTablecountMeasure_EOV =VAR currentselection=SELECTEDVALUE('Unique'[Unique Date])VAR Tablecount=CALCULATE(COUNT('Table'[Serial Number]),'Table'[EoV Date]=currentselection,ALLEXCEPT('Table','Table'[EoV Date]))RETURNTablecount
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.
2 Replies
MoetazzahranResolver II
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]))RETURNTablecountMeasure_EOV =VAR currentselection=SELECTEDVALUE('Unique'[Unique Date])VAR Tablecount=CALCULATE(COUNT('Table'[Serial Number]),'Table'[EoV Date]=currentselection,ALLEXCEPT('Table','Table'[EoV Date]))RETURNTablecount
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.It worked like a charm!!! thanks I've been struggling with these a couple of weeks and now you saved me.
