Forum Discussion

khaycock's avatar
khaycock
Helper I
6 years ago

Combining Data

Hello,   Is it possible to create extra lines based on data in other columns? I have three fields that I need to be one field so it can be a generic filter:   I'm conscious that amending th...
Need Help
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi khaycock ,

 

We can try to use the following measure to meet your requirement:

 

1. unpivote those columns

 

 

 

2. remove the attribute columns

 

 

3. remove duplicate values in value column

 

 

4. then you can merge with other queries:

 


Best regards,

  • khaycock's avatar
    khaycock
    Helper I
    6 years ago

    Hi v-lid-msft 

    Thanks for the help. The only issue is that the rest of this table holds keys so I can't just remove duplicates as it will remove data. The unpivot thing worked initially but because it then duplicated the ID, it messed up the relationship. Here is a snippet of the whole table's data so you can see what I mean. 

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi khaycock ,

       

      If you want to create a slicer and if it select Sussex, then the if will show the record with row 1, 2, 5, 6, 8, 11 and 13, we can create a calculated table as slicer:

       

      Slicer =
DISTINCT (
    UNION (
        DISTINCT ( 'Table'[Target List 1] ),
        DISTINCT ( 'Table'[Target List 2] ),
        DISTINCT ( 'Table'[Target List 3] )
    )
)

       

      Then we can create a measure and use it in visual filter to meet your requirement:

       

      Filter In Date =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        [Target List 1] IN FILTERS ( Slicer[Target List 1] )
            || [Target List 2] IN FILTERS ( 'Slicer'[Target List 1] )
            || [Target List 3] IN FILTERS ( Slicer[Target List 1] )
    )
)

       

       

       

      Or we can create a duplicate query of table in Power Query Editor, then remove other column and use the unpivote solution to create such a slicer table

       

       

      Please also refer to this similar thread: https://community.powerbi.com/t5/Desktop/Use-Same-Filter-for-Multiple-Columns/td-p/880356


      Best regards,

       

       

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Please show the exact result you are expecting.

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi khaycock ,

       

      How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


      Best regards,