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

Need Help

10 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      khaycock
      Helper I
      6 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

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