Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

EARLIER Function not working

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!
Need Help
  • AlB's avatar
    AlB
    3 years ago

    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.

     

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    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.

     

  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    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.

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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 
        )
  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    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.