Adding filters to a calculated column

[eem_2021]

Hello! 

I am working on a project where my data  is being pulled as a text (I unforntunelty cant change this). I made some formulas in order to have it be converted to an integer but those appear as calculated columns, which does not allow me to add filters to. I want to find a way where I can filter out any value that is under "x" value. Any help would be awesome! 

[Olufemi7]

Hello @eem_2021,

Alternative solution: In addition to the marked answer, here’s another approach that works well when your profit values are stored as text and you want to filter out anything above 120.

Calculated columns won’t respond to filters, you need a measure instead. For example:

Profit ≤ 120 Total =
SUMX (
    FILTER (
        'Table',
        VALUE('Table'[Profit (Text)]) <= 120
    ),
    VALUE('Table'[Profit (Text)])
)

In Power BI Desktop, add this to a Card or Table visual. In Power BI Service, use a slicer on Profit (Text) (set to ≤ 120) and add the measure to a Table visual.

If you want the cutoff to be dynamic, create a small ThresholdTable with values (50, 75, 100, 120, 150) and use it as a slicer. Update the measure with SELECTEDVALUE() so users can pick the threshold interactively.

Here’s how it looks in Power BI Service,  using a slicer and table visual to filter and display totals:

Slicer and Table Visual

[Praful_Potphode · Accepted solution]

Hi @eem_2021 ,

If you want to pass slicer or filter value to your visual or calculations, the you can use DAX functions SELECTEDVALUE,HASONEVALUE,FILTERS etc.

these will work with measures not calculated columns .but if you want specific condition rows you can create a measure with flag one or zero.something like below example:

Is Active in Period = 
VAR PeriodStart = MIN('Date'[Date])  // The start date from your slicer
VAR PeriodEnd   = MAX('Date'[Date])  // The end date from your slicer
VAR EmpStart    = SELECTEDVALUE('User Posting'[Start Date])
VAR EmpEnd      = SELECTEDVALUE('User Posting'[End Date])

RETURN
// Logic: An employee is active if they started before the period ended...
// ...AND (they are still active OR they left after the period started).
IF (
    EmpStart <= PeriodEnd && 
    (ISBLANK(EmpEnd) || EmpEnd >= PeriodStart),
    1,
    0
)

above measure will give me records empstart and empend is withing slicer selection.

If you want to work with calculated columns use string manipulation functions to create the desired columns/flags.

References:

https://community.fabric.microsoft.com/t5/Desktop/Using-measure-as-a-filter/td-p/2996922 

https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/ 

https://learn.microsoft.com/en-us/dax/containsstring-function-dax 

https://learn.microsoft.com/en-us/dax/containsstringexact-function-dax 

https://learn.microsoft.com/en-us/dax/selectedvalue-function-dax 

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[v-venuppu]

Hi @eem_2021 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @eem_2021 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @amitchandak @FBergamaschi @xifeng_L @syahmisi98 @GeraldGEmerick for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[amitchandak]

@eem_2021 , if you are looking to pass a filter or slicer value to calculated columns, that is not possible. Calculated column are calculated at load time and will not consider report filter or slicer.

You need create a measure , if that can serve the purpose . Measure by default takes filter and slicer values 

[xifeng_L]

Hi @eem_2021 ,

You can use Visual Filter Panel to add filter:

Or add filter conditions on measure:

CALCULATE(
    ...,
    Table[Column]=xxxx
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[GeraldGEmerick]

@eem_2021 Can you clarify your intent here? Are you saying that you want to convert it to an integer and then filter out any row where the value is < x value or are you saying that during the conversion you don't want to convert "numbers" less than x value and instead return something like null instead? Are you able to paste your Power Query or DAX code that you are using? Are these DAX calculated columns or Power Query columns or are they measures in a table and thus display as columns?

[eem_2021]

Thank you for your reply. I created a visual where I have three columns for profit. The data set I'm using has profit as a text. I am using basic convert () function to make it an integer. However I want to be able to exclude any field that populates that is over 120. I'm running into the issue where I am not able to add filters to calculated columns 

[FBergamaschi]

To add filter to a calculated column you need to wrap a CALCULATE statement around its code

Column Filtered = 
CALCULATE ( 
            Original column DAX code,

            Table[Column] > 120
)

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

[syahmisi98]

Because your Profit field comes in as text, the best approach is to convert it to a proper numeric column in Power Query, then filter out values above 120 directly in the report.

- Convert the column's data type to Decimal Number in Power Query

- Apply the filter to your visual:

1. Select the visual (table, matrix, chart, etc.)

2. Go to the Filters pane (on the right)

3. Drag Profit column into Filters on this visual

4. Set:

• Show items when the value
  → is less than or equal to
  → 120

This will automatically remove any rows where Profit > 120.