Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create A Slicer

Hi,

 

I had several Project Nos beginning with year.

 

Kindly assist on how to create a slicer using the Project Nos

 

//NT

 

 

Need Help
  • zenisekd's avatar
    zenisekd
    2 years ago

    Well, you didnt ask the right question on the first post. 

    You will have to create a new column, either by DAX, or in Power Query (which I would recommend). you can use following steps in PQ:  Duplicate the column, Split the column by "-" remove the second part and use the first part as the year. 

    In Dax you can use LEFT function when creating new column.

    Kudos and mark as solution appreciated.

  • mickey64's avatar
    mickey64
    2 years ago

    Step 0: I use your data.

     

    Step 1: I duplicate 'ProjectNr' column on Power Query Editor.

    - Before -

    - After -

     

    Step 2: I split the 'ProjectNr - Copy' column by '-' delimiter.

    - Before -

    - After -

     

    Step 3: I rename 2 columns below.

        'ProjectNr - Copy.1' --> 'Year'

        'ProjectNr - Copy.2' --> 'Nr'

     

    Step 4: I click 'Close & Apply'.

     

    Step 5: I make 2 slicers and a matrix on Power BI Desktop.

    - Year -

    - ProjectNr -

    - Matrix -

     

     

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

       

      Hi,

       

      I had done what you said and it showed the all project nos as shown. But what I'm trying to get is the slicer will only show the year and the table outline the job nos. do i need to create a new columns if so what dax i need to create?

       

       

      • zenisekd's avatar
        zenisekd
        Super User
        2 years ago

        Well, you didnt ask the right question on the first post. 

        You will have to create a new column, either by DAX, or in Power Query (which I would recommend). you can use following steps in PQ:  Duplicate the column, Split the column by "-" remove the second part and use the first part as the year. 

        In Dax you can use LEFT function when creating new column.

        Kudos and mark as solution appreciated.

  • ajohnso2's avatar
    ajohnso2
    Solution Supplier
    2 years ago

    create a new column

    ProjectYear = 

    MID('Projects'[ProjectNo],1,4)

     

    This will return just the year portion, add this new column as your slicer

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      do i need to create a new column in power bi or power query?

  • mickey64's avatar
    mickey64
    Super User
    2 years ago

    Step 0: I use your data.

     

    Step 1: I duplicate 'ProjectNr' column on Power Query Editor.

    - Before -

    - After -

     

    Step 2: I split the 'ProjectNr - Copy' column by '-' delimiter.

    - Before -

    - After -

     

    Step 3: I rename 2 columns below.

        'ProjectNr - Copy.1' --> 'Year'

        'ProjectNr - Copy.2' --> 'Nr'

     

    Step 4: I click 'Close & Apply'.

     

    Step 5: I make 2 slicers and a matrix on Power BI Desktop.

    - Year -

    - ProjectNr -

    - Matrix -