Hello, I have the following model each table with the following values media1_idmedia2_idrelation12SEQUEL21PREQUEL34RETELLING43RETELLING67SIDE-STORY76SIDE-STORY28SEQUEL82PREQUEL mediaiduseridstatus 11completed21completed41completed61completed62completed73completed81planning mediaidname1Godfather 2Godfather 23The Mummy (1932 film)4The Mummy (1999 film)5Titanic6Breaking Bad7Better Call Saul8godfather 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)

samotfrt

Frequent Visitor

2 years ago

Solved

Table visual with both active and inactive relationship

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)

Need Help

Anonymous

2 years ago

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

5 Replies

Anonymous

Not applicable

2 years ago

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

Table visual with both active and inactive relationship.pbix37 KB

samotfrt

Frequent Visitor

2 years ago

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

Not applicable

2 years ago

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

amitchandak
Super User
2 years ago
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-power-bi

Forum Discussion

Table visual with both active and inactive relationship

5 Replies

Related content

Use calendar's inactive relationship in matrix visual?

Problem with inactive relationships

Filter Table Visual by EndDate (Inactive Relationship)

Inactive relationships

relationship active & inactive