Table visual with both active and inactive relationship

[samotfrt]

Hello,

I have the following model

each table with the following values

media1_id	media2_id	relation
1	2	SEQUEL
2	1	PREQUEL
3	4	RETELLING
4	3	RETELLING
6	7	SIDE-STORY
7	6	SIDE-STORY
2	8	SEQUEL
8	2	PREQUEL

mediaid	userid	status
1	1	completed
2	1	completed
4	1	completed
6	1	completed
6	2	completed
7	3	completed
8	1	planning

mediaid	name
1	Godfather
2	Godfather 2
3	The Mummy (1932 film)
4	The Mummy (1999 film)
5	Titanic
6	Breaking Bad
7	Better Call Saul
8	godfather 3

Now, I am trying to make a table visual that would display the media related to the media that the user have completed , like the following for the user 1

I can make the following table but then i  cannot "vlookup" the name of the media2 neither can I filter out the rows in which the media2 was already completed by the user (first 2 rows)

[Anonymous]

Hi @samotfrt ,

I created a sample pbix file(see the attachment), please check if that is what you want.

media2_name = 
CALCULATE (
    MAX ( 'media'[name] ),
    FILTER (
        ALL ( 'media' ),
        'media'[mediaid] = SELECTEDVALUE ( 'relations'[media2_id] )
    )
)

Flag = 
VAR _uids =
    ALLSELECTED ( 'userlist'[userid] )
VAR _media1 =
    SELECTEDVALUE ( 'relations'[media1_id] )
VAR _media2 =
    SELECTEDVALUE ( 'relations'[media2_id] )
VAR _tab1 =
    CALCULATETABLE (
        VALUES ( 'userlist'[mediaid] ),
        FILTER (
            ALLSELECTED ( 'userlist' ),
            'userlist'[userid]
                IN _uids
                    && 'userlist'[status] = "completed"
        )
    )
VAR _tab2 =
    CALCULATETABLE (
        VALUES ( 'userlist'[mediaid] ),
        FILTER (
            ALLSELECTED ( 'userlist' ),
            'userlist'[userid]
                IN _uids
                    && 'userlist'[status] <> "completed"
        )
    )
VAR _count =
    CALCULATE (
        COUNT ( 'relations'[media1_id] ),
        FILTER (
            ALLSELECTED ( 'relations' ),
            'relations'[media1_id] = _media1
                && 'relations'[media1_id] IN _tab1
        )
    )
RETURN
    IF ( _count = 1 || ( _count >= 1 && _media2 IN _tab2 ), 1, 0 )

Best Regards

[samotfrt]

Thank you  for the help.

However I noticed that when the _count = 1 it still displays relations to watched media. For example the first row shouldn't be displayed because media2_id = 2 was watched by the user.

[Anonymous · Accepted solution]

Hi @samotfrt ,

I updated the pbix file(see the attachment), please check if that is what you want.

Flag = 
VAR _uids =
    ALLSELECTED ( 'userlist'[userid] )
VAR _media1 =
    SELECTEDVALUE ( 'relations'[media1_id] )
VAR _media2 =
    SELECTEDVALUE ( 'relations'[media2_id] )
VAR _tab1 =
    CALCULATETABLE (
        VALUES ( 'userlist'[mediaid] ),
        FILTER (
            ALLSELECTED ( 'userlist' ),
            'userlist'[userid]
                IN _uids
                    && 'userlist'[status] = "completed"
        )
    )
VAR _tab2 =
    CALCULATETABLE (
        VALUES ( 'userlist'[mediaid] ),
        FILTER (
            ALLSELECTED ( 'userlist' ),
            'userlist'[userid]
                IN _uids
                    && 'userlist'[status] <> "completed"
        )
    )
VAR _count =
    CALCULATE (
        COUNT ( 'relations'[media1_id] ),
        FILTER (
            ALLSELECTED ( 'relations' ),
            'relations'[media1_id] = _media1
                && 'relations'[media1_id] IN _tab1
        )
    )
RETURN
    IF ( NOT(_media2 in _tab1) &&( _count = 1 || ( _count >= 1  && _media2 IN _tab2 )), 1, 0 )

Best Regards

[samotfrt]

Thanks.

I changed the final line to

IF ( NOT(_media2 in _tab1) &&(  _count >= 1 ), 1, 0 )

Because i think the (_media2 IN _tab2) only considers shows in the user list, that are not "completed". So, if for example media_id 8, that in the sample i provided is in the list of the user as "Planning",  were to not be in the user list, it wouldn't show up.

[amitchandak]

@samotfrt , Based on what I got. You have use userelationship in measure to use inactive relationship

calculate( countrows('realtion'),USERELATIONSHIP ('realtion'[media2_id], 'Media'[Mediaid]))

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...