Revising a measure for a time series

[gambleave]

I have a scenario as follows... there is a long list of applicants for membership to a selective club.  Existing members periodically submit votes to indicate the applicants that they support for membership. But different members may submit their votes at different times. 

The table VoteHistory records this vote history in the columns: Voter / Applicant / Date of Vote

It is connected to a standard date table. 

An applicant's total current votes is the count of votes they received from the last received votes across the voter pool. For example, if Voter A's records received 11/11/2022 indicate a record for Applicant B, but Voter A's records received 10/02/2023 do not, then the applicant had a vote from the member in between those dates, but not after 10/02/2023. 

I have the following measure that counts an applicant's total current votes across the membership. But how can it be revised to use in a line chart that will show an applicant's total current votes over time?

Total Current Votes =
CALCULATE(
DISTINCTCOUNT('VoteHistory'[Voter]),
FILTER(
'VoteHistory',
'VoteHistory'[Applicant] = SELECTEDVALUE('VoteHistory'[Applicant]) &&
'VoteHistory'[Date of Vote] =
CALCULATE(
MAX('VoteHistory'[Date of Vote]),
ALLEXCEPT('VoteHistory', 'VoteHistory'[Voter])
)
)
)

[jdbuchanan71 · Accepted solution]

That is very strange, not sure what feature would be driving the error.  I put your measure into my file and it worked fine.  If you open my file on your computer do you get the error?  My file is linked to this post.  If it is from the version of PowerBI it would throw the error when you open my file.
The only thing I can think is it doesn't like the compare on the two date columns using &&.  Maybe try it like this?

Vote History = 
VAR _Start = MIN ( Dates[Date] )
VAR _End = MAX ( Dates[Date] )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( VoteHistory, VoteHistory[Voter], VoteHistory[Applicant] ),
            VoteHistory[Date of Vote] <= _End,
            ( VoteHistory[Vote Valid Through] >= _Start || ISBLANK ( VoteHistory[Vote Valid Through] ) )
        )
    )

[gambleave]

You were right that removing the && fixed the issue. Thanks so much. 

[jdbuchanan71]

Please copy the measure from your data model exactly as you wrote it and paste it here.

[gambleave]

Here it is, but it's really just copied from your measure. The only other difference can tell is that the applicant column in my VoteHistory table contains applicants' names, whereas you had used numbers.  And as mentioned, my version of Power BI Desktop is from Jan 2021. 

Applicant Votes = 
VAR _Start = MIN ( Dates[Date] )
VAR _End = MAX ( Dates[Date] )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( VoteHistory, VoteHistory[Voter], VoteHistory[Applicant] ),
            VoteHistory[Date of Vote] <= _End
            && ( VoteHistory[Vote Valid Through] >= _Start || ISBLANK ( VoteHistory[Vote Valid Through] ) )
        )
    )

[gambleave]

Just to add that I tried replicating your exact data, data model and measure at work and I arrive at the same error. So perhaps it has to do with a newer version of Power BI Desktop being required? Unfortunately i only have access to version 2.88.1382.0 (January 2021) 😞

[jdbuchanan71]

Please copy the measures from your data model exactly as you wrote them or better yet, share your .pbix file (post it to dropbox or one drive and put the link here).

Yes, the measure can be used to make a line chart to show how many votes an applicant has over time.

[gambleave]

Hi there... I am very perplexed because I basically copied your measure, so it should be exactly the same. I also have the same data model setup, except that there are several extra columns in the two tables (but that should have no bearing?) and my Dates table has the columns Date/DayOfWeek/MonthNumber/Quarter/Year.    

The file is unfortunately within my company network and I have no way to share it. The only thing I can think of is that I'm using a Jan 2021 version of Power BI Desktop (the latest I can get at work - hence I cannot open your .pbix on it). Could that have something to do with it? I also tried to replicate with Excel PowerPivot and arrive at the same error (The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

Grateful for any further suggestions...

[jdbuchanan71]

For Total Current Votes (votes as of today) you can either filter the report to today, the [Applicant Votes] mesure will show only votes valid today or, you can write another measure to expressly call it out, like this.

Total Current Votes = 
CALCULATE ( [Applicant Votes], ALL ( Dates ), Dates[Date] = TODAY() )

[jdbuchanan71]

@gambleave 

First, in order for my solution to work we need to disconnect the Dates table from the votes table.  That way we can use a measure to count all the votes for an Applicant at whatever date range we want.

Then we a column to the Votes table of 'Vote Valid Through' which, for any vote is the date before the voters next submission.  That way, if a voter A submits for Applicant 1 on 11/11/2022 but does not include Applicant 1 on his next vote on 10/02/2023 we know his vote for Applicant 1 was valid from 11/11/2022 - 10/01/2023.

The new calculated column on the VoteHistory table looks like this.

Vote Valid Through = 
VAR _DateOfVote = CALCULATE ( SELECTEDVALUE ( VoteHistory[Date of Vote] ) )
VAR _NextVote = 
    CALCULATE ( 
        MIN ( VoteHistory[Date of Vote] ), 
        ALLEXCEPT ( VoteHistory, VoteHistory[Voter] ), 
        VoteHistory[Date of Vote] > _DateOfVote 
    )
RETURN
IF ( ISBLANK ( _NextVote ), BLANK(), _NextVote -1 )

Then we write a measure to count the votes for a Applicate in the date range we are looking at.

Applicant Votes = 
VAR _Start = MIN ( Dates[Date] )
VAR _End = MAX ( Dates[Date] )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( VoteHistory, VoteHistory[Voter], VoteHistory[Applicant] ),
            VoteHistory[Date of Vote] <= _End
            && ( VoteHistory[Vote Valid Through] >= _Start || ISBLANK ( VoteHistory[Vote Valid Through] ) )
        )
    )

This counts the unique voter/applicant pairs in whatever date range we are looking at and, in the case of the voters most recent submission the VoteHistory[Vote Valid Through] column will be blank so we count that one as valid from the vote date forward.

In your example we can see voter A counts for applicant 1 up until his next submission where applicant 1 drops out.  He shows for applicant 1 in October-2023 because, on October 1st, he was still voting for applicant 1.

I have attached my sample file for you to look at.

[gambleave]

Hi there and thank you so much for your help. This solution is a bit beyond my current level of DAX comprehension. With my own data modeL, I have taken your approach of creating the 'Vote Valid Through' calculated column in the VoteHistory table. However, when I create the Applicant Votes measure, I get the error "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table."  Any idea what causes this?

Assuming the error can be resolved...wIthout the link to the Date table, would I be able to select an applicant and have a line chart to show their "total current votes" over time?