Combining Data

[khaycock]

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 the data and adding extra lines with the same data will upset the relationship as this is a table that holds keys.

Is there a way to do this?

[v-lid-msft]

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]

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]

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,

[Ashish_Mathur]

Hi,

Please show the exact result you are expecting.

[v-lid-msft]

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,

[Greg_Deckler]

You could create a query to get each column (3 queries) and then use an Append query to append them all together.

[khaycock]

Hi Greg,

Does the above query work to append? I'm not sure how to append a manually created query as it's not in the Power Query Editor?

[amitchandak]

Refer:https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Also in DAX we have union function

[az38]

Hi @khaycock 

try a new calculated table

Table 2 = FILTER(
DISTINCT(
UNION(
SELECTCOLUMNS('Table',"Target List", 'Table'[Target List 1]),
SELECTCOLUMNS('Table',"Target List", 'Table'[Target List 2]),
SELECTCOLUMNS('Table',"Target List", 'Table'[Target List 3])
)
), NOT(ISBLANK([Target List])))

[khaycock]

How can I connect this to the rest of the data so I can use it to filter?