Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
joseclaudio
New Member

Help counting values from one table to another table

‎06-17-2024 11:30 AM

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 NumberEoL Date 2EoV Date
FCH2048FT8PNot Published yetNot Published yet
FCH17018VD32024Prior 2024
WZP20350SSANot Published yetNot Published yet
FOC2133U12T20262025
WZP20350SYPNot Published yetNot Published yet
FCH193093GNNot Published yetNot Published yet
FOC2010S2B120282027
FCH1504DVMXPrior 2024Prior 2024
FCW2018D09820252025
FGL2019X04D20242024
FJC2405M4JN20272027
MXQ13800PFNot Published yetNot Published yet
FCH1346AK4PPrior 2024Prior 2024
FCW2336PQWG20272027
FCH1508A1PVPrior 2024Prior 2024
FCW2132NBV020272027
WZP20350GCHNot Published yetNot Published yet
FCH193093CBNot Published yetNot Published yet
FCW2142NG2420272027
FOC2016W1VK20292029
FCH2127DQZVNot Published yetNot Published yet
FCW2336PRBL20272027
WZP20350GDPNot Published yetNot Published yet
FCH2048FQ27Not Published yetNot Published yet
WZP20350T5MNot Published yetNot Published yet
FCH192981ZJNot Published yetNot Published yet
FTX1923S4HT20242024



Table B:

DateCount of EoLCount 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:

 

FCH17018VD32024Prior 2024

 

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

Message 1 of 3
217 Views
0
1 ACCEPTED SOLUTION
Moetazzahran
Resolver II
Resolver II

‎06-17-2024 03:59 PM

Hello @joseclaudio,

Moetazzahran_0-1718664783576.png

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.

View solution in original post

Message 2 of 3
213 Views
0
2 REPLIES 2
Moetazzahran
Resolver II
Resolver II

‎06-17-2024 03:59 PM

Hello @joseclaudio,

Moetazzahran_0-1718664783576.png

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.

Message 2 of 3
214 Views
0
joseclaudio
New Member
In response to Moetazzahran

‎06-18-2024 10:16 AM

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

Message 3 of 3
166 Views
0

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

View All