Forum Discussion
Helper IISlicer for filtering several columns
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'
Helper IIHi FBergamaschi
The view and metric would be the name of my slicers and the options for each slicer are in different colunm so i want to groud them in the slicers and those will affect my visuals.
- View: Colunms: BAT_STATUS_CUST,BAT_STATUS_SHPR,BAT_STATUS_SHPR_INTL,BAT_STATUS_PAYR,BAT_STATUS_PAYR_INTL
- Metric: colunms: LY_SHIPMENTS,CY_SHIPMENTS,LY_PACKS,CY_PACKS,LY_REVENUE,CY_REVENUE,LY_BILL_WGT,CY_BILL_WGT
- hnguy71
Super User
3508026
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
- 3508026
Hi 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'