Calculated columns with calculate function and variables - with or without filter function

[Kasinaama]

As the title states, I have this reoccurring problem with undestanding when to use filter function and when not to in calculated column in calculate function that uses variables. I've tried watching videos and googleing but most of them talk about measures so if someone could explain to me like im 5 so I could finally get it 😅

Scenario examples from my work simplified:

Why doesn't this work?:

Duplicate =

VAR CurrentRow = 'Table'[ID]

RETURN

IF(

CALCULATE(

COUNTROWS('Table'),

'Table'[ID]= CurrentRow

) > 1,

"Yes",

"No"

)

And why this does?:

Duplicate =

VAR CurrentRow = 'Table'[ID]

RETURN

IF(

CALCULATE(

COUNTROWS('Table'),

FILTER(

'Table',

'Table'[ID]= CurrentRow

)

) > 1,

"Yes",

"No"

)

And also why this does?:

ComboFound =

VAR Color = 'Table'[Color]

VAR Size = 'Table'[Size)

RETURN

IF(

CALCULATE(

COUNTROWS('Table2'),

'Table2'[Size]= Color

'Table2[Size] = Size

) > 0,

"Yes",

"No"

)

[Kasinaama]

@FBergamaschi "Answer: I do not know what you mean by "work" but the above code does not go, in the CALCULATE part, to impose the current ID to a row of the table Table but to a row of a table made out of all the distinct ID, overrideing filters that might already be there, so if there are two rows in the table Table with the same ID, they are all contracted into one row"

I'm sorry, I have still hard time undestanding this. Why does it impose the current ID to a row of a table made out of all the distinct ID? Why is it not the full table itself? And how does that differ when CurrentRow is changed to a constant value? Because that DAX can somehow understand and returns only proper rows.

[FBergamaschi]

Hey @Kasinaama 

the point is knowing DAX and its syntax sugar

When you write this

Duplicate =

VAR CurrentRow = 'Table'[ID]

RETURN

IF(

CALCULATE(

COUNTROWS('Table'),

'Table'[ID]= CurrentRow

) > 1,

"Yes",

"No"

)

What really is executed is

Duplicate =

VAR CurrentRow = 'Table'[ID]

RETURN

IF(

CALCULATE(

COUNTROWS('Table'),
FILTER ( ALL ( 'Table'[ID] ),

'Table'[ID]= CurrentRow
)

) > 1,

"Yes",

"No"

)

so DAX automatically finds the least expensive materialization, only the column on which you impose a condition. If you want the table (as you need in this case), you need to explicitely ask to go row by row in the full table and you did in this syntax

Duplicate =

VAR CurrentRow = 'Table'[ID]

RETURN

IF(

CALCULATE(

COUNTROWS('Table'),

FILTER(

'Table',

'Table'[ID]= CurrentRow

)

) > 1,

"Yes",

"No"

)

If you want to understand more, you need to study CALCULATE, I suggest SQLBI courses / books

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Kasinaama]

Hey @FBergamaschi 

Okay so, I understand that this FILTER ( ALL ( 'Table'[ID] ) removes all filters but does it also somehow lose the value stored in a variable? Because if I write 

CALCULATE(

COUNTROWS('Table'),

'Table'[ID]= 123

) > 1,

 it works as excepeted right? But if the '123' comes in form of a variable, it does not work?

[FBergamaschi]

HI @Kasinaama 

the FILTER ( ALL ( 'Table'[ID] ) does not remove filters (this is a very old story of misunderstading DAX that I am trying to clear all over the world) but ignores filters, which is not the same thing but might look identical sometimes

The value can come from a variable of from one or more fixed value of your choice

Hope you can consider this solved now

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[v-tejrama]

Hi @Kasinaama ,

Great follow-up!
You’ve totally got the main idea down.

Just to clear things up the variable isn’t losing its value. What you’re noticing actually comes from how ALL('Table'[ID]) works inside FILTER(). Using ALL wipes out any filters or slicers on [ID], so the context gets reset.

When you set up a variable like:

VAR selectedID = 123
RETURN
CALCULATE(
    COUNTROWS('Table'),
    FILTER(ALL('Table'[ID]), 'Table'[ID] = selectedID)
)

you’re just reapplying the filter with that variable. If you do it like this, it works the same as using 'Table'[ID] = 123 directly.

So you’re spot on the change in results is because ALL() removes the context, not because the variable goes away.
I’ve attached the .pbix file and screenshots for your reference.

Please feel free to text me back if you have any questions.

Thank you

[Kasinaama]

@v-tejrama Okay so maybe I don't fully understand what removing context mean.

Can you try to pinpoint where my thought process goes wrong? 😅

In this example:

Duplicate =

VAR CurrentRow = 'Table'[ID]

RETURN

IF(

CALCULATE(

COUNTROWS('Table'),
FILTER ( ALL ( 'Table'[ID] ),

'Table'[ID]= CurrentRow
)

) > 1,

"Yes",

"No"

)

Being calculated column, it starts from the first row (row-context) and variable gets ID as its first value. It goes inside the CALCULATE where it clears context and gives full ID column and filters it down to values that are same as CurrentRow (in this case first row value) and counts them. Then it moves to the second row and CurrentRow gets value of the ID in the second row and does the same things than in the first row and so on. Except it does not? Does it even work like this if the CurrentRow is static value like 123?

[v-tejrama]

Hello again @Kasinaama ,

Great follow-up awesome question again!

You’re totally on the right track with your explanation and you’ve nailed most of the main ideas. The only tricky bit is how row context and filter context play together inside CALCULATE, especially with ALL().

So, in your calculated column, CurrentRow = 'Table'[ID] grabs the row context just fine. But when you use FILTER(ALL('Table'[ID]), ...), here’s what’s happening:

ALL('Table'[ID]) wipes out any filters on [ID], including the row context filter. That means the table is basically unfiltered at that point. Then, inside FILTER, you’re adding back the filter to match 'Table'[ID] to CurrentRow.

Here’s the key: this works because FILTER goes through all rows, finds the ones where the ID matches the current row, and counts them. So your understanding of how the calculated column loops through each row is pretty much spot on.

The only misconception is thinking ALL() stops CurrentRow from working it doesn’t! The variable still has the right ID for that row. ALL() just resets the filter context, so you have to reapply the row filter yourself in the FILTER function.

• CurrentRow does its job as expected.
• ALL() clears filter context, not variable values.
• Your logic is solid as long as you know you’re manually putting the filter back in.
  
  

Please feel free to text me back if you have any questions.

Thank you

[Kasinaama]

@v-tejrama hey sorry, but I feel really stupid..

So variable works fine, it has the right value for every row. Thats good. But I still don't get this part: "Your logic is solid as long as you know you’re manually putting the filter back in."

Does this not put the filter back in?

'Table'[ID]= CurrentRow

If the variable does have correct value as you said it gets it from the row context. Or does it "forget" it as soon as ALL is used and gets all confused even though I define the variable outside the CALCULATE function?

Also to ensure:
writing this inside the CALCULATE function:

FILTER ( ALL ( 'Table'[ID] ),

'Table'[ID]= CurrentRow)

is same as this:

'Table'[ID]= CurrentRow

right? it just does the FILTER(ALL(...)) thing behind the curtains?

[v-tejrama]

Hi @Kasinaama ,

Thank you @FBergamaschi  for the helpful input!

Were you able to resolve the issue? If the response addressed your query, kindly confirm. This helps keep the community informed and improves solution visibility.

Thank you for your support!

[Kasinaama]

@v-tejrama Well kind of. The problem was about my understanding of the subject and while you provided good information for sure, I didn't fully understand it yet.

[v-tejrama]

Hi @Kasinaama ,

Thank you for your feedback. DAX can be challenging, especially with context transitions. If you let me know which part is still unclear, I can explain it further or provide a simpler example to help clarify.

Thank you.

[v-tejrama]

Hi @Kasinaama ,

Thanks for clarifying. DAX concepts like this can be a bit tricky in the beginning. Just let me know which part is still not clear, and I’ll break it down step-by-step or give a simpler example so it’s easier to understand.

Thank you.

[Kasinaama]

Hey @v-tejrama, my message number 10 was the one I was still wondering

[v-tejrama]

Hi @Kasinaama ,

Your variable (CurrentRow) always remembers the right row value. It never lost even if you use ALL(). What ALL() does is simply clears any filters that were already applied to the column/table. Think of it as starting fresh with the whole column or table.

When you write this inside CALCULATE:

'Table'[ID] = CurrentRow

DAX internally rewrites it to:

FILTER ( ALL ( 'Table'[ID] ), 'Table'[ID] = CurrentRow )

Which means that they are the same thing. That is why sometimes it feels like DAX is “only looking at distinct IDs” instead of the full table, because under the hood, that’s exactly what it is doing.

If you need to check for duplicates, the short version doesn’t work because it only looks at distinct IDs. That’s why you need to use

FILTER ( 'Table', 'Table'[ID] = CurrentRow )

Here you are checking the full table row by row, so duplicates are counted properly.

Thank you,

Tejaswi.

[v-tejrama]

Hi @Kasinaama ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Kasinaama]

Sorry for late response. And thank you very much for trying to help and I somewhat undestand it now. One thing is still a bit mystery to me. So in the code below, if the table is filtered to rows of only distinct ID values, why does it sometimes still return "Yes" when there are multiple ID's and sometimes not?

Duplicate =

VAR CurrentRow = 'Table'[ID]

RETURN

IF(

CALCULATE(

COUNTROWS('Table'),

'Table'[ID]= CurrentRow

) > 1,

"Yes",

"No"

)

[v-tejrama]

Hi   @Kasinaama ,

It sometimes returns “Yes” and other times it doesn’t because 'Table'[ID] = CurrentRow is actually processed as FILTER(ALL('Table'[ID]), …), which only looks at unique IDs. In certain situations, you’re only seeing one row, so the count is 1. To reliably identify duplicates, use FILTER('Table', 'Table'[ID] = CurrentRow) so that all rows are included.

And thank you for your continued efforts in testing the approaches we’ve provided. As some time has passed and multiple solutions have been attempted without achieving the desired outcome, we kindly suggest raising a support ticket with the Microsoft product team. They will be able to investigate the matter further and provide more in-depth assistance to help resolve the issue.

You can raise a support ticket using the following link: Submit a product support request

Additionally, if you do find a resolution through the support channel, we would sincerely appreciate it if you could share your findings with the community. Your insights may help other members facing similar challenges.

Thank you for your patience and understanding. Please continue to engage with the Fabric Community for any further questions or support needs.

[Kasinaama]

I'm sorry if I wasn't clear. I do know what is the correct solution, my question is more about why it works the way it does. Because if I understand the logic, I can use it in every situation rather than in this specific case.

The question I had was, why it sometimes sees only one row and sometimes not? What are those certain situations? So I know when to use it and when not.

[v-tejrama]

Hi  @Kasinaama ,

If the issue still persists on your end, we recommend reaching out to our Power BI certified solution partners. These are highly experienced professionals who can provide in-depth technical assistance and offer tailored solutions based on your specific needs. You can explore the list of trusted partners here:

Power BI Partners | Microsoft Power Platform

You’re always welcome to continue engaging with the community as well,
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread.

we truly appreciate your active participation in the Microsoft Fabric Community.

[wardy912]

Hi @Kasinaama 

Calculated columns are row-by-row calculations. Each row has its own row context.
CALCULATE changes or adds filter context, but row context doesn't automatically become filter context unless you explicitly convert it — and that’s where FILTER() comes in.

Duplicate =
VAR CurrentRow = 'Table'[ID]
RETURN
IF(
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[ID] = CurrentRow
    ) > 1,
    "Yes",
    "No"
)

This doesn't work because 'Table'[ID] = CurrentRow is not wrapped in a FILTER() function.
In a calculated column, this condition is evaluated outside of the row context — so it doesn’t behave like you think.
DAX doesn’t know you want to filter the whole table to rows where 'Table'[ID] = CurrentRow.

Duplicate =
VAR CurrentRow = 'Table'[ID]
RETURN
IF(
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[ID] = CurrentRow
        )
    ) > 1,
    "Yes",
    "No"
)

This works because FILTER('Table', 'Table'[ID] = CurrentRow) creates a new filter context.
Now DAX understands: “Filter the whole table to rows where ID matches the current row’s ID.”

ComboFound =
VAR Color = 'Table'[Color]
VAR Size = 'Table'[Size]
RETURN
IF(
    CALCULATE(
        COUNTROWS('Table2'),
        'Table2'[Color] = Color,
        'Table2'[Size] = Size
    ) > 0,
    "Yes",
    "No"
)

This also works because colour and size are scalar variables, and you're using them in simple column = value filters.
DAX can optimize this and treat it like a filter context, but this only works when the expressions are simple and unambiguous.

Use Filter() when:
You're in a calculated column and want to filter a table based on the current row.
You're using complex logic or need to refer to row context.

Dont use Filter() when:

You're using simple scalar values in CALCULATE (like Column = Variable).
You're writing a measure.

I hope this clears it up for you. Please give a thumbs up and mark as solved if it does, thanks!