DAX Conditional Lookup

[Peejz_Jalmasco]

Hi!

I've got a table that look like this:

ID	SL CODE	Status
1	SL0001	Current
1	SL0002	Current
2	SL0003	Past Due
2	SL0004	Current
3	SL0005	Past Due
3	SL0006	Past Due

I need a dax measure that will tag the IDs its final status, the rule is that whenever there are Past Due SL in an ID, all of it will be considered Past Due. Here is the must be result table based on the example above:

ID	Status
1	Current
2	Past Due
3	Past Due

Thank you!

[Nathaniel_C · Accepted solution]

Hi @Peejz_Jalmasco 

Stats = CALCULATE(MAX('YourStatus'[Status]),ALLEXCEPT('YourStatus','YourStatus'[ID]))

my table is "YourStatus'
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @Peejz_Jalmasco 

Stats = CALCULATE(MAX('YourStatus'[Status]),ALLEXCEPT('YourStatus','YourStatus'[ID]))

my table is "YourStatus'
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Peejz_Jalmasco]

Hi @Nathaniel_C 

I'm sorry but the column 'Status' is from a measure and is not a column, i don't think that the MAX function will run in a measure -- MAX([Status])

[Nathaniel_C]

Hi @Peejz_Jalmasco , what does the measure look like?

[Peejz_Jalmasco · Accepted solution]

@Nathaniel_C 

Thanks for your help. I've solved this using the MAXX function to convert the measure "[Account Status2]"

Final Status = CALCULATE(MAXX(VALUES('PN MASTER DATA'[SLCODE]),[Account Status2]), ALLEXCEPT('PN MASTER DATA','PN MASTER DATA'[NAME]))

[Nathaniel_C]

Hi @Peejz_Jalmasco 
Or with just the id.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel