Forum Discussion

Fabio_B's avatar
Fabio_B
Frequent Visitor
2 years ago
Solved

Simple column filter

Hello all,

this is my very first post, so nice to meet you all.
I'm approaching Power Bi since a while, but still struggling with this filter.
I have an excel file like this:

 

What i would like to achieve is a drop down filter that filters the column of the table above, in order to select only the data that I need (basically energy or gas country related) and to show it in a simple chart.

I've tried to look on-line but still not able to achieve it.
Can someone please help me to understand how to fix this?
Many thanks in advance for your kind support.


BR


Fabio

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Fabio_B ,
    Thanks to quantumudit and Greg_Deckler  previous quality answers, here's my solution to your new question
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:


    Following the previous steps I accomplished the following effect

    In order to add or remove line segments automatically, you can create a meaure and use filed parameter to achieve this effect.
    Create meaures

    Sum of FRANCE GAS = SUM('Table'[FRANCE GAS])
    Sum of FRANCE Power = SUM('Table'[FRANCE Power])
    Sum of ITALY GAS = SUM('Table'[FRANCE GAS])
    Sum of ITALY Power = SUM('Table'[ITALY Power])

    Based on these meaure to create filed parameter
    In order to realize the use of a filed parameter to control two different visual objects, first you need to make sure that the order of the two parameters is the same, and second you need to create a one-to-one relationship between the two parameters based on the Parameter order

    Final outputt

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     



     



     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Fabio_B Right, so you need to use a Slicer (it's the visual usually two rows up from the bottom that has a funnel in the icon). You can switch it from list mode (default) to a dropdown.

  • quantumudit's avatar
    quantumudit
    Super User
    2 years ago

    Hello Fabio_B 

     

    Welcome to the community, You need to do the following steps:

     

    PowerQuery Steps

    • Keeping the "Month-Year" column unpivot the rest of the columns,i.e., the "Country Metric" column. You'll end up with 3 columns, i.e., "Month-Year", "metric," and "value."
    • Now, Split the "metric" column by space delimiter and you'll get the "Country" and "Metric" columns separately
    • Similarly, split the "Month-Year" column by "-" delimiter and you will end up with the "Month" and "Year" columns
    • Trim for any whitespace in columns and other unnecessary columns, and provide appropriate datatype
    • You will eventually end up with a dataset with the following columns:
      • Month
      • Year
      • Country
      • Metric
      • Value

    Power BI

    • Load the dataset, and provide the "Metric" column as a dropdown slicer (It should contain Power/Gas value)
    • You can any chart or, table to dynamically visualize the result

     

    Hope these steps will help you solve the issue, but let us know if the issue persists.

     

    Best Regards,
    Udit

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudo 👍

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

    • Fabio_B's avatar
      Fabio_B
      Frequent Visitor
      2 years ago

      Thanks for your quick reply quantumudit  and Greg_Deckler !
      I've solved this issue creating a field (New parameter, Fields) with the column that i was looking for.

      Now i'm able to filter by year (month, quarter etc...) and also by Country of interest:

       

      I was thinking it was ok but i'm struggling now to create a responsive chart, linked to the table above.
      The table is created like this:


      I would like to achieve a line chart, that will automatically add and remove lines according to the filter selected, as well as per adjusting itself according to the timing selected.
      Do you think is something feasible?


      Thanks again for any feedback you will share.

       

      Fabio

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Fabio_B ,
    Thanks to quantumudit and Greg_Deckler  previous quality answers, here's my solution to your new question
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:


    Following the previous steps I accomplished the following effect

    In order to add or remove line segments automatically, you can create a meaure and use filed parameter to achieve this effect.
    Create meaures

    Sum of FRANCE GAS = SUM('Table'[FRANCE GAS])
    Sum of FRANCE Power = SUM('Table'[FRANCE Power])
    Sum of ITALY GAS = SUM('Table'[FRANCE GAS])
    Sum of ITALY Power = SUM('Table'[ITALY Power])

    Based on these meaure to create filed parameter
    In order to realize the use of a filed parameter to control two different visual objects, first you need to make sure that the order of the two parameters is the same, and second you need to create a one-to-one relationship between the two parameters based on the Parameter order

    Final outputt

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly