Forum Discussion
Adding filters to a calculated column
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
Memorable Membereem_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?
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
- syahmisi98
Advocate I
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:
Select the visual (table, matrix, chart, etc.)
Go to the Filters pane (on the right)
Drag Profit column into Filters on this visual
Set:
Show items when the value
→ is less than or equal to
→ 120
This will automatically remove any rows where Profit > 120.
- FBergamaschi
Super User
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
