Forum Discussion

3508026's avatar
3508026
Icon for Helper II rankHelper II
6 months ago
Solved

Slicer 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 options are in different columns.
I need also create a slicer for filtering the number of quarters.
- Slicer for filtering the number of quarters based on Rolling Quarter column, so if I select the number I always get the last Rolling quarter available that in this case is 'Nov25-Jan26'
1 > Nov25-Jan26
3 > Aug25-Oct25, Nov25-Jan26
4 > Feb25-Apr25, May25-Jul25, Aug25-Oct25, Nov25-Jan26

Data_classification 
Thanks in advance and regards,

 

Need Help
  • hnguy71's avatar
    hnguy71
    6 months ago

    Hi 3508026 

    Yes, you can use field parameters. Although, not really ideal but it's possible.

     

    1. On the ribbon bar, under MODELING select 'New Parameter' and then select 'Fields'

       

    2. Create a name for your parameter, for example, 'View' and add all the fields you want to be part of that parameter.
    3. 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'

       

    4. Finally, drop the 'Fields' column to any of your visual(s).

     

     

    I have attached a sample pbix file for you:

7 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    6 months ago

    Hello

    a slicer filters a single column at a time, you can also insert more than one (from a single table) to create a kind of hierarchy

    .

    I am not sure what you mean by view and metric, if they are columns, you can create one slicer for each and/or combine them into one. Otherwise you need to consolidate the columns in a single one.

     

    If on the contrary you want to dinamically choose the measure to compute and/or the cloumns to group in a visual, then you need a Field Parameter (Modeling -> Parameter -> Fields)

     

    Please can you clarify

     

    Thanks

     

    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

  • 3508026's avatar
    3508026
    Icon for Helper II rankHelper II
    6 months ago

    Hi 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's avatar
      hnguy71
      Icon for Super User rankSuper User
      6 months ago

      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's avatar
        3508026
        Icon for Helper II rankHelper II
        6 months ago

        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.

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    6 months ago

    Hi 3508026,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you FBergamaschi and hnguy71 for the prompt response.

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      6 months ago

      Hi @3508026,

       

      We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

       

      Thanks and regards,

      Anjan Kumar Chippa