FILTER function does not work

[Anonymous]

Hello

I want to count the number of instances of the value in Col1 inside the Col1 after it is filtered based on a value for Col2.

I try to use this calculated column but it does not work:

Column = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Col1]=SELECTEDVALUE(Table1[Col1]) && Table1[Col2]="SOME"))

It returns the count of the value in Col1 in the whole table and not inside the table that is filtered by Table1[Col2]="SOME".

Any idea please?

[Anonymous]

Hi @Anonymous ，

You need a measure instead of a column,would you pls provide sample data with expected output for us to check the measure?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[edhans]

Calculated Columns are not dynamic. They calculate only one time, when the data is loaded. Filtering, slicers, selecting data, crossfiltering, etc. all have no effect on calculated columns. They are not like columns in Excel that do recalc.

You need to redo this as a measure, and we'd need more info including sample data as to what you are trying to do to assist.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query