Power BI - calculated columns

[Bassmansam]

Hi All

I've been going up the wall with trying to understand this concept in the last few days.

This is a calculated column and there are two tables, LearningPlan (1) and Activities (Many)

I understand how ALL works to remove filters in a visual but in a calculated column i'm lost.
I know that context transition introduces a filter context for the current row but don't we want to keep that context so we can find id's from the current row in the Activities row?

It seems like thats what is happening anyway except at first we remove the filters, only to put them back on.

I just don't get why?

CALCULATE(
MAX(Activities[Submission date]) ,
FILTER ( ALL ( 'Activities'[LearnerID]) ,
 'Activities'[LearnerID] = LearningPlan[LearnerID])
)

 

[dazzerd]

I don't think you can use all in that way. All and those other hellish filter things on bi are really for use in measures which will be applied to visuals.

2 ways to solve this problem. The proper way is in m query do a group by statement on learner id and max submisson date, call it last submission date. to create a new table. You can then relate that table if you wish to learner I'd in activities. Then your calculated column in activities is related(last submission date). Or don't relate the table and just do a lookup.

If you really wanted you can just also do the same using a calculated Dax table. But they can cause problems with relationships to real table down the line if you are unsure of what you are doing so best to use m query.

Clearly using m query you could also do an inner joint back to activities and actually add the last submission date back into activities as well.

 

[dazzerd]

But saying that if you are adding your calculated column to your learner plan table and it's related 1 to many with activities. You can just use max(submisson date) and it should work and it implicitly knows the relationship.

 

[Bassmansam]

I don't think you can use all in that way. All and those other hellish filter things on bi are really for use in measures which will be applied to visuals.

2 ways to solve this problem. The proper way is in m query do a group by statement on learner id and max submisson date, call it last submission date. to create a new table. You can then relate that table if you wish to learner I'd in activities. Then your calculated column in activities is related(last submission date). Or don't relate the table and just do a lookup.

If you really wanted you can just also do the same using a calculated Dax table. But they can cause problems with relationships to real table down the line if you are unsure of what you are doing so best to use m query.

Clearly using m query you could also do an inner joint back to activities and actually add the last submission date back into activities as well.

I should have mentioned this is code my predecessor wrote, it does seem to work but i'm not sure why you would use ALL here unless the column is going to be used as a visual but in that case write it as a measure?

Yea ill either re write this as a measure or in power query thanks.

 

[Bassmansam]

But saying that if you are adding your calculated column to your learner plan table and it's related 1 to many with activities. You can just use max(submisson date) and it should work and it implicitly knows the relationship.

Yep i have done this before with similar things when i was starting out i BI. So when i came across the code in OP with all i was just like wtf is ALL for here?

 

[dazzerd]

Yep there are a lot of ways to do the thing in power bi
Depending on what you require, I.e. if you had a visual table
You could just add a column from learner plan table and then add submission date from activities and use an auto measure and you can set the submisson date to last or first anyway.

You only really need it as a specified column if you are going to use it as a filter or do something else with it downstream.