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
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,