Forum Discussion

PBI_Member_01's avatar
PBI_Member_01
Helper III
3 years ago
Solved

Create Calculated Column based on multiple values from another table

Hi,

I have a Dataset set to DirectQuery Storage Mode and have the following case on hand:

 

 
So I need to create a calculated column in Table A which will return Profile IDs present in Table A and Table C but not in Table D (which in the above mentioned case are 5,6,7)

I also want to filter the Profile IDs in Table A by Table B 'Test_Org' = 'N' and only need to consider those Profile IDs as I traverse towards Table D.

I am a beginner Power BI user so I am not sure what would be the right approach. I have tried using RELATED function, to filter my Profile IDs by 'Test_Org' = 'N' and to my understanding, this was only successful because RELATED can be used on 1-to-many relationship with the calculated column being created on 'many' side. But If I want to go towards Table C, It doesn't highlight the table name for me.

Can someone please help me out with this or guide me on how to take this through? I have tried explaining the problem on hand but if there is anything missing, please let me know.

Thanks in advance

Regards

  • tamerj1's avatar
    tamerj1
    3 years ago

    PBI_Member_01 
    Please try the following measure

    Falg1 = 
SUMX ( 
    VALUES ( TableA[Profile_ID] ),
    CALCULATE ( 
        VAR TestOrg = CALCULATE ( SELECTEDVALUE ( TableB[TEST_ORG] ), CROSSFILTER ( TableA[ORG_ID], TableB[ORG_ID], BOTH ) ) 
        VAR AProfile = SELECTEDVALUE ( TableA[Profile_ID] )
        VAR CProfiles = CALCULATETABLE ( VALUES ( TableC[Profile_ID] ) )
        VAR DProfiles = CALCULATETABLE ( VALUES ( TableD[Profile_ID] ) )
        VAR Result =
            IF ( 
                TestOrg = "N",
                IF ( 
                    AProfile IN EXCEPT ( CProfiles, DProfiles ),
                    1
                )
            )
        RETURN
            Result
    )
)

    You can also place the measure only in the filter pane of the visual and select "Is not blank" then apply the filter. Then it will show like this

15 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi PBI_Member_01 
    You mentioned: "So I need to create a calculated column in Table A" therefore, I assume you want to create a flag column in TableA. Please refer to attached sample file

    Falg = 
VAR TestOrg = RELATED ( TableB[TEST_ORG] )
VAR AProfile = TableA[Profile_ID]
VAR CProfiles = CALCULATETABLE ( VALUES ( TableC[Profile_ID] ) )
VAR DProfiles = CALCULATETABLE ( VALUES ( TableD[Profile_ID] ) )
VAR Result =
    IF ( 
        TestOrg = "N",
        IF ( 
            AProfile IN EXCEPT ( CProfiles, DProfiles ),
            1,
            0
        ),
        0
    )
RETURN
    Result

     

    • PBI_Member_01's avatar
      PBI_Member_01
      Helper III
      3 years ago

      Hi tamerj1 ,

      Thank you for your quick response on this.

      I actually wanted to fetch these values (5,6,7) in a column, instead of a flag. But I think this will work as well as I can simply drag all the Profile IDs and apply a visual level filter with Flag set to 1. But for the sake of discussion, Is it possible for us to fetch only those values (5,6,7) and have them in a calculated column?

      In the meantime, I am going to test this out and will update you shortly on the outcome.

      Thank you so much for providing assistance on this.

      Kind Regards

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        PBI_Member_01 

         Of course we can but in which row would you like to disply the result? Or by "calculate column" you mean a measure and you want to disply the result on a card visual? Still not clear to me. Please clarify. Thank you