Need Help with data

[Mahmoud23]

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 that shows the correct total number of artists and is also interactive when I use filters. For example, if I select a song with three artists, the card should display three.

Can I create a separate table and how do I link it to the original table? When I created the relationship, the one-to-many setup is correct, but the card does not interact.

I have attached an example of part of the table, and I may provide a link to the full data

[Anonymous · Accepted solution]

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

[Mahmoud23]

https://docs.google.com/spreadsheets/d/16qDlNKnisbS_NyLqz7d8yrOo2wK5xInl/edit?usp=sharing&ouid=10520... 

Help me please!

I have a data table from Spotify that contains a column recording the artists & another the number of artists, and some cells have more than one artist. I want a function or method to create a card in power bi that shows the correct total number of artists and is also interactive when I use filters. For example, if I select a song with three artists, the card should display three.

Can I create a separate table and how do I link it to the original table? When I created the relationship, the one-to-many setup is correct, but the card does not interact.

I  provided a link to the full data. 

& in your opinion, what are the best additional modifications i can make to this data to extract good results that i can use in a power bi report.

[parry2k]

@Mahmoud23 duplicate post - I already replied to the original post/

[Mahmoud23]

I Attached the data in a new post as the first solution didn't work with me 

[parry2k]

@Mahmoud23 provide more details, what didn't work? Your reply is not helpful what is not working?

[parry2k]

@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]

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