Forum Discussion
3508026
Helper II
Helper IISlicer for filtering several columns
Hello, I need to create slicer for filtering the data that are in different columns: - Slicer for filtering the view: The optiona are in different columns. - Slicer for filtering the metric: The o...
Hi 3508026
Yes, you can use field parameters. Although, not really ideal but it's possible.
- On the ribbon bar, under MODELING select 'New Parameter' and then select 'Fields'
- Create a name for your parameter, for example, 'View' and add all the fields you want to be part of that parameter.
- Repeat for as many different parameters you may need or have. Then, if you don't have 'View Hidden' on your model enabled already, for each of those new parameters, right click the table, and click 'View Hidden'
- Finally, drop the 'Fields' column to any of your visual(s).
I have attached a sample pbix file for you:
- On the ribbon bar, under MODELING select 'New Parameter' and then select 'Fields'
hnguy71
Super User
Super User3508026
In order to do that you'll likely need to unpivot your data first.
Here's a sample code you can plug into powerquery:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZhdT9swFIb/Cuo1WP62c1na8DFBi1YYY4iLrGRdtK6pQpjg389O4yQEmtRpjETtExNCHh2f9/jt/f3gbnA4OP6mBkzVMHxeYHY0naeYqSuIdASzeHKhBvWj7z9dxotgqReDNIpXWejfmJuyaHito3l8oKZx+CtMkvBRhVdBMg/17eerNAmO/OckXof6zpd1nKT6Cf7t5v/ov/NHF+cTv1iF6kPymeYzohAggaEHPbNCKPDw4OHQMdvLWrNdJVGcROlrH2yoNhMICITI8xjLVzCgxCXZ2HeTNVabEZEAQyExLrKGOfC8D9gug1fF9uV5mbPpaA+2/rOGazMTgGIhKKPSsKktylyiZdE4/hs+pdF8C17l15XQdksyBgg3wBRgV1g3MzM0ZyxM5vEqjVbhKt08oWutCQYkh0gQbpRFACwLuuEoq0c1nIQ/Fd1wneR0OoKYmxe/q+P5p2/AhlfZoyY/WgttK9rFdHbdMn1ESIUEEEJelBxHgKEaIdqfsEn+T5IwWvxOG6ptR7h60SH1goALIqmRGK4khvIaHnaL16olO9KZChOm6jACkiFJkNmcSmEA92p0pFc6e6XsmDtJgISSQogNL2LVPreB0+ST+J9WlmCVASllUZEt3HHHFtex6pAqOtXEPcgKON0fanCsJ7jRmduyq9NJzlVrE5gZTaFKVJSCvqXjbulcpQ5j1bclJpyZekSUA1KvOtETXdezV2fFhABKQlmxgItjynd1eTZVg8A5HM3gWAWOVuH0HovC5ePBLFiGT++uGvTFhvT06/S2nKod3toZfArhJpc2CtpMaOEPHPN13avNfFYu4TMIW47U3Sht/IJjyO6+wW6jfuwa3sK1eFgruIp7aKzBrUdsO7wG7+COsQcjYUfZ4B805e1QDVKvt/g/0wwvg+RPqF5rod98PHq/O7e0ifbDTOn3yijvhO8WrM1EyYp6Z7UXVFtUK2dRohr12eGMY4Vqo6r7ZrXJZpSkpEfSrlbDltTGc5SkFoaqlbSz79gXtcmAlKgW9qorqvOkNrqREtXCa7WiVr8xdYm6szV5+A8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COMPANY = _t, #"CUSTOMER NAME" = _t, #"CUSTOMER ID" = _t, ROLLING_Q = _t, SEG_MONTH = _t, CTRY_ENTI_CTRY_CD = _t, SHIPPER = _t, PAYER = _t, SEGMENT = _t, SEGMENT_DETAIL = _t, ORIG_RGN_CD = _t, ORIG_CTRY_CD = _t, DEST_RGN_CD = _t, DEST_CTRY_CD = _t, PRODUCT_SIMPLE = _t, PRIORITY_DEFERRED = _t, PARCEL_FREIGHT = _t, IC_IE_DOM = _t, IMPORT_EXPORT = _t, BAT_STATUS_CUST = _t, BAT_STATUS_SHPR = _t, BAT_STATUS_SHPR_INTL = _t, BAT_STATUS_PAYR = _t, BAT_STATUS_PAYR_INTL = _t, LY_SHIPMENTS = _t, CY_SHIPMENTS = _t, LY_PACKS = _t, CY_PACKS = _t, LY_REVENUE = _t, CY_REVENUE = _t, LY_BILL_WGT = _t, CY_BILL_WGT = _t]),
#"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"LY_SHIPMENTS", "CY_SHIPMENTS", "LY_PACKS", "CY_PACKS", "LY_REVENUE", "CY_REVENUE", "LY_BILL_WGT", "CY_BILL_WGT"}, "Metric", "Amount"),
#"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Unpivoted Only Selected Columns", {"BAT_STATUS_CUST", "BAT_STATUS_SHPR", "BAT_STATUS_SHPR_INTL", "BAT_STATUS_PAYR", "BAT_STATUS_PAYR_INTL"}, "View", "Status"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Only Selected Columns1",{{"Amount", type number}})
in
#"Changed Type"
And once you load it, you can do something like this:
I've attached a sample pbix for you to reference
3508026Helper II
Helper IIHi hnguy71
Thank you. The structure you suggested would be ideal, but my dataset is already very large, over 17 million rows. Is there another option? I’ve never used Parameters before, so I’m not sure if they could be the right solution.
- hnguy71
Hi 3508026
Yes, you can use field parameters. Although, not really ideal but it's possible.
- On the ribbon bar, under MODELING select 'New Parameter' and then select 'Fields'
- Create a name for your parameter, for example, 'View' and add all the fields you want to be part of that parameter.
- Repeat for as many different parameters you may need or have. Then, if you don't have 'View Hidden' on your model enabled already, for each of those new parameters, right click the table, and click 'View Hidden'
- Finally, drop the 'Fields' column to any of your visual(s).
I have attached a sample pbix file for you:
- On the ribbon bar, under MODELING select 'New Parameter' and then select 'Fields'