Forum Discussion

samotfrt's avatar
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 RETELL...
Need Help
  • Anonymous's avatar
    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

samotfrt's avatar
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's avatar
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

  • samotfrt's avatar
    samotfrt
    Frequent Visitor
    2 years ago

    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.