Forum Discussion

Mahmoud23's avatar
Mahmoud23
Regular Visitor
1 year ago
Solved

Need Help with data

Help with data. I have a data table from Spotify that contains a column recording the number of artists, and some cells have more than one artist. I want a function or method to create a card ...
Data Protection
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from parry2k , please allow me to provide another insight:
    Hi  Mahmoud23 ,

    I couldn't open your pbix link, I created my own relevant sample data:

    Here are the steps you can follow：

    1. Duplicate the table in Power Query, delete columns for splitting into multiple columns, select all columns for Unpivot Columns.

    Select [track_name] -- Remove

    Table_copy – select [act_name] – Split column – By Delimiter.

    Select all columns – Unpivot Columns.

    Select [Attribute] – Remove.

    Select [Value] – Duplicate Column.

    2. Create measure.

    Measure =
var _select=SELECTEDVALUE('Table'[track_name])
var _art=MAXX(FILTER(ALL('Table'),'Table'[track_name]=_select),[art_name])
var _table=
FILTER('Table_copy',CONTAINSSTRING(_art,[Value])=TRUE())
var _table2=
ADDCOLUMNS(
    _table,"Count",COUNTX(FILTER(ALL('Table'),CONTAINSSTRING('Table'[art_name],[Value])=TRUE()),[track_name]))
return
CONCATENATEX(_table2,[Value]&"-"&[Count]," ")

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

parry2k's avatar
parry2k
Super User
1 year ago

Mahmoud23 there are few approaches on how you can solve it?

 

One approach is:

 

- add an index column to the table - calling track table

- refer to track table (after the index column is added), let's call it artist table

- remove all other columns except index and artist column from artist table

- split artist column by delimiter (comma) and under advance options (of split dialog box), select rows

- close and apply

 

now you have two tables one is called title and the other is artist. set relationship between title and artist tables on the index column, and set the cross filter direction to both (not recommended but for testing let's do it)

 

visualize your data, and in the slicer use artist column from the artist table, as you select different artists, it will show track based on the select artists (or visual will be interactive)

 

Hope this helps, tweak the solution as you see fit.

  • Mahmoud23's avatar
    Mahmoud23
    Regular Visitor
    1 year ago

    Thanks but i already did that way & That didn't Work 😓