Create a DAX Measure to Count Supporters by Latest Vote Batch in Power BI
Question details
The user needs a Power BI DAX measure that counts current supporters of an employee based on each voter's latest vote batch submitted before a selected date.
- Product
- Microsoft Power BI (DAX)
- Device & OS
- not provided
- Scenario
- The user is building a Power BI report to analyze voter support for employees over time and needs the measure to respect a selected date.
- Observed behavior
- Cumulative vote counts are not acceptable; support must reflect only each voter's latest submitted batch before the selected date.
Make sure your Power BI data model has a dedicated Date table related to the vote batch date and relationships from Votes to Employees and Voters. Otherwise the measure cannot respond correctly to the date slicer or the selected employee.
Create a Power BI DAX Measure That Counts Supporters from Each Voter’s Latest Batch
Use this measure when your vote history table contains multiple batch submissions and you must count an employee’s current supporters only from the latest batch before the selected date.
The measure first calculates the latest submission date for each voter in the current date context. It then counts only voters whose latest batch includes the selected employee, so later batches that omit an employee automatically remove that voter from the supporter count.
In your Power BI report, open the Modeling tab in the ribbon and click New measure to create a measure in the selected table.
Replace table and column names to match your model, then paste the following DAX formula: Supporters Count = VAR SelectedDate = MAX('Date'[Date]) VAR SelectedEmployee = SELECTEDVALUE(Employee[EmployeeID]) VAR LatestBatches = ADDCOLUMNS( VALUES(Votes[VoterID]), "LatestBatchDate", CALCULATE( MAX(Votes[BatchDate]), Votes[BatchDate] <= SelectedDate ) ) VAR CurrentSupporters = SUMX( FILTER(LatestBatches, [LatestBatchDate] <> BLANK()), VAR CurrentVoter = [VoterID] VAR CurrentLatestDate = [LatestBatchDate] RETURN CALCULATE( DISTINCTCOUNT(Votes[VoterID]), Votes[VoterID] = CurrentVoter, Votes[BatchDate] = CurrentLatestDate, Votes[EmployeeID] = SelectedEmployee ) ) RETURN COALESCE(CurrentSupporters, 0)
Add Employee Name and the new measure to a table visual, then add a Date slicer. The measure will recalculate the supporter count for the selected employee and date context.
Test a voter who changed support in a later batch. The measure should show the selected employee only when that employee is in the voter’s latest batch before the selected date.
WPS Office: A Free, Lightweight Office Alternative for Everyday Data and Document Work
While DAX measure tuning belongs to Microsoft Power BI, WPS Office is a free, lightweight office suite that opens Microsoft Excel, Word, and PowerPoint files with high compatibility. It offers a familiar ribbon interface and easy migration for users who need everyday spreadsheet, document, and presentation work without the cost.
Frequently Asked Questions
How can I show 0 instead of blank when an employee has no supporters?
The measure wraps the SUMX result in COALESCE(...,0), so it returns 0 when no voting rows match the selected employee and date context. Check that your visual does not have an additional filter hiding the measure result.
What should I do if my vote table uses SubmittedDate instead of BatchDate?
Replace every Votes[BatchDate] reference in the measure with your actual date column, such as Votes[SubmittedDate], and update the relationship to the Date table accordingly.
Does the measure work when a date range is selected instead of a single date?
The measure uses MAX('Date'[Date]) as the selected date, so with a range it evaluates up to the latest date in the slicer context. For a different period-end behavior, change SelectedDate to the appropriate period calculation.
Why does a voter disappear from an employee’s count after a later batch?
Because the measure compares each vote row only to the voter's latest batch date before the selected date. If the later batch does not include that employee, no row qualifies and the voter is not counted.
Can I use this measure for multiple employees in a matrix or bar chart?
Yes. The SELECTEDVALUE(Employee[EmployeeID]) expression returns the current employee in the visual context, so the measure calculates separately for each employee row, column, or axis value.
