EARLIER Function not working

[Anonymous]

Dear Power BI heroes,

I have a question regarding the EARLIER function. I have the following DAX query:

Unieke waarde =

    CALCULATE(DISTINCTCOUNT('AUDIT line'[Amnt]), FILTER(ALL('AUDIT line'), 'AUDIT line'[Amnt] = EARLIER ( 'AUDIT line'[Amnt] ) && CALCULATE(COUNT('AUDIT line'[Amnt]), ALL('AUDIT line')) = 1)).

The problem here is that Power BI does not accept my input parameters for the EARLIER function and displays the message: "Parameter is not the correct type". The table and column do really exist and I checked for typos but that also does not seem to be the case. What am I doing wrong? I have studied examples and in those examples, the same kind of input parameters are inserted in the EARLIER function. Any help would be appreciated!

[AlB]

@Anonymous 

or yet another, more elegant, option:

Unieke waarde V4 = 
COUNTROWS(    
    FILTER (
        ALL ( 'AUDIT line'[Amnt] ),
        CALCULATE (COUNT ( 'AUDIT line'[Amnt] )) = 1
    )
)

Unieke waarde V5 = 
SUMX (
        ALL ( 'AUDIT line'[Amnt] ),
        1*(CALCULATE (COUNT ( 'AUDIT line'[Amnt] )) = 1)
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

@Anonymous 

An alternative version that uses EARLIER as you initially intended:

Unieke waarde V3 = 
CALCULATE (
    DISTINCTCOUNT ( 'AUDIT line'[Amnt] ),
    FILTER (
        ALL ( 'AUDIT line' ),
        CALCULATE (COUNT ( 'AUDIT line'[Amnt] ), 'AUDIT line'[Amnt] = EARLIER('AUDIT line'[Amnt] ), ALL('AUDIT line')) = 1
    )
)

 or the same using variables instead of EARLIER, which is recommended nowadays:

Unieke waarde V3B = 
CALCULATE (
    DISTINCTCOUNT ( 'AUDIT line'[Amnt] ),
    FILTER (
        ALL ( 'AUDIT line' ),
        VAR amnt_ = 'AUDIT line'[Amnt]
        RETURN
        CALCULATE (COUNT ( 'AUDIT line'[Amnt] ), 'AUDIT line'[Amnt] = amnt_ , ALL('AUDIT line')) = 1
    )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

Hi @Anonymous 

If this is a measure, there is no previous/earlier row context in your code that EARLIER can refer to and thus fails.

I assume you are trying to calculate the number of values in the 'AUDIT line'[Amnt] column that appear only once in the table. If so:

Unieke waarde V2 = 
CALCULATE (
    DISTINCTCOUNT ( 'AUDIT line'[Amnt] ),
    FILTER (
        ALL ( 'AUDIT line' ),
        CALCULATE (COUNT ( 'AUDIT line'[Amnt] ), ALLEXCEPT ( 'AUDIT line', 'AUDIT line'[Amnt] )) = 1
    )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Greg_Deckler]

@Anonymous There's no row context for EARLIER to operate on that provides that column. You could do this instead:

    VAR __Amnt = MAX('AUDIT line'[Amnt])
    VAR __Result = 
    CALCULATE(
        DISTINCTCOUNT('AUDIT line'[Amnt]), 
        FILTER(
            ALL('AUDIT line'), 
            'AUDIT line'[Amnt] = __Amnt 
        )