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
Super UserHello 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