Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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,
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.
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,
Hi,
Please show the exact result you are expecting.
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,
You could create a query to get each column (3 queries) and then use an Append query to append them all together.
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?
Refer:https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Also in DAX we have union function
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])))
How can I connect this to the rest of the data so I can use it to filter?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |