Calculated Column Get next Value per Group in same Table

[joshua1990]

Hi community!

I have a table that contains dimensional data and values:

Article	Step	Info	Next Info
AA	1	X1	X2
AA	2	X2	X3
AA	3	X3	X3
AB	1	B1	B2
AB	2	B2	B2

This table contains 3 columns:

• Article: Name of Article
• Step: Step Number
• Info: Text Information

Now I would like to add a 4th column that shows me per Article the next Information based on the Step.

If it is the last step then the information for the current step should be displayed.

How is this possible using DAX?

[AlB · Accepted solution]

Hi @joshua1990

NextInfo =
VAR nextStep_ =
    CALCULATE (
        MIN ( Table1[Step] ),
        Table1[Step] > EARLIER ( Table1[Step] ),
        ALLEXCEPT ( Table1, Table1[Article] )
    )
RETURN
    IF (
        ISBLANK ( nextStep_ ),
        Table1[Info],
        CALCULATE (
            DISTINCT ( Table1[Info] ),
            Table1[Step] = nextStep_,
            ALLEXCEPT ( Table1, Table1[Article] )
        )
    )

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.

[joshua1990 · Accepted solution]

@AlB : Thanks a lot! It is not working:

"A table of multiple values was supplied where a single value was expected.".

I guess we need also the article r as a filter since since the steps are different between the articles. 

[AlB · Accepted solution]

Hi @joshua1990

NextInfo =
VAR nextStep_ =
    CALCULATE (
        MIN ( Table1[Step] ),
        Table1[Step] > EARLIER ( Table1[Step] ),
        ALLEXCEPT ( Table1, Table1[Article] )
    )
RETURN
    IF (
        ISBLANK ( nextStep_ ),
        Table1[Info],
        CALCULATE (
            DISTINCT ( Table1[Info] ),
            Table1[Step] = nextStep_,
            ALLEXCEPT ( Table1, Table1[Article] )
        )
    )

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.

[joshua1990 · Accepted solution]

@AlB : Thanks a lot! It is not working:

"A table of multiple values was supplied where a single value was expected.".

I guess we need also the article r as a filter since since the steps are different between the articles. 

[AlB · Accepted solution]

@joshua1990 

It already has the filter on the article: through context transition and the ALLEXCEPT().

It does work in the data you have provided. See the attached file.

If you get that error it means that your data has more than one value in the column Info for the same article-Step combination. You'd need to review your data or decide what to do in those cases: which Info value do you keep? If for instance you want to keep the first one alphabetically, it suffices with using MIN instead of the DISTINCT on the code    

NextInfo =
VAR nextStep_ =
    CALCULATE (
        MIN ( Table1[Step] ),
        Table1[Step] > EARLIER ( Table1[Step] ),
        ALLEXCEPT ( Table1, Table1[Article] )
    )
RETURN
    IF (
        ISBLANK ( nextStep_ ),
        Table1[Info],
        CALCULATE (
            MIN ( Table1[Info] ),
            Table1[Step] = nextStep_,
            ALLEXCEPT ( Table1, Table1[Article] )
        )
    )

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.