Forum Discussion
Post ProdigyCalculated Column Get next Value per Group in same Table
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?
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.
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.
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.
3 Replies
- AlB
Community Champion
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.
joshua1990AlB : 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
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.