Hi @hitesh1607,
>>I am trying to create a Filter for the user where he can sort a report or visual by clicking options like 'Sales less than 40% or Qty change more than 10%'.
Based on your description, I suggest to create a calculated table, and then create a measure for each filter. Since I don't have your sample, I did the following based on my sample.
1.Create a calculated table contains COLUMN A ,B,C as your description in your first post.
Table =
ADDCOLUMNS (
SUMMARIZE ( 'Sales OrderDetails', 'Sales OrderDetails'[productid] ),
"QTY", CALCULATE ( SUM ( 'Sales OrderDetails'[qty] ) ),
"Salesamonut", 'Sales OrderDetails'[Saleamount]
)
2.Create measures for all your filter(To save time I only created three measures):
A<30 = IF( MAX('Table'[QTY]) <1000,1,0)
3.Create a table for slicer on these measures (How to use measures for slicer, please refer tohttps://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/😞
4.Create a measure for filter on the visual:
Measure =
VAR SELECTEDVALUE =
SELECTEDVALUE ( Table2[Slicer] )
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE = "A<30", [A<30],
SELECTEDVALUE = "D", [D],
SELECTEDVALUE = "C<50", [C<50]
)
5.Add the measure to the visual level filter:
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU_kkYgRWzVJr3b7nm...
Best Regards,
Dedmon Dai
Hi @hitesh1607,
>>I am trying to create a Filter for the user where he can sort a report or visual by clicking options like 'Sales less than 40% or Qty change more than 10%'.
Based on your description, I suggest to create a calculated table, and then create a measure for each filter. Since I don't have your sample, I did the following based on my sample.
1.Create a calculated table contains COLUMN A ,B,C as your description in your first post.
Table =
ADDCOLUMNS (
SUMMARIZE ( 'Sales OrderDetails', 'Sales OrderDetails'[productid] ),
"QTY", CALCULATE ( SUM ( 'Sales OrderDetails'[qty] ) ),
"Salesamonut", 'Sales OrderDetails'[Saleamount]
)
2.Create measures for all your filter(To save time I only created three measures):
A<30 = IF( MAX('Table'[QTY]) <1000,1,0)
3.Create a table for slicer on these measures (How to use measures for slicer, please refer tohttps://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/😞
4.Create a measure for filter on the visual:
Measure =
VAR SELECTEDVALUE =
SELECTEDVALUE ( Table2[Slicer] )
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE = "A<30", [A<30],
SELECTEDVALUE = "D", [D],
SELECTEDVALUE = "C<50", [C<50]
)
5.Add the measure to the visual level filter:
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU_kkYgRWzVJr3b7nm...
Best Regards,
Dedmon Dai
Calculated columns should be avoided if at all possible. Push this back into Power Query. Put this code in a Blank Query to see what I did:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJQ0lFyAWJDAwOlWJ1oJWOQgBMQG0H4ZiC+IxCbQfimMA3GEL6RKdwACN8ciR8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"COLUMN A" = _t, #"COLUMN B" = _t, #"COLUMN C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"COLUMN A", Int64.Type}, {"COLUMN B", type text}, {"COLUMN C", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "A < 30", each [COLUMN A] < 30, type logical),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "D", each [COLUMN B] = "D", type logical),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "C < 50", each [COLUMN C] < 50, type logical),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "A < 30 && D && C < 50", each [#"A < 30"] and [D] and [#"C < 50"], type logical),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "A < 30 || D || C < 50", each [#"A < 30"] or [D] or [#"C < 50"], type logical)
in
#"Added Custom4"1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
It looks like this when done:
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
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
