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 ...
  • 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

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

  • PBI_Member_01's avatar
    PBI_Member_01
    Helper III
    3 years ago

    For calculated Column, I would like to drop Profile ID column and the new calculated column in a table matrix side by side, and the calculated column should only populate values against those rows where the flag would be 1 (based on the logic you have proposed above). And if I remove the Original Profile ID column, it should only show 3 rows which would be 5,6,7.

    As far as the Measure is concerned, simple COUNT of these IDs would be sufficient to display on a card visual giving the result 3, based on the above example.

    • PBI_Member_01's avatar
      PBI_Member_01
      Helper III
      3 years ago

      Okay so I tried your solution on the Production Environment but when I am creating this Column, I am getting an error being highlighted on the following points:

      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
        )

       It says CALCULATETABLE is not a function.
      Same for VALUES and EXCEPT.

      Also, For VAR CProfiles, VAR DProfiles, It does not let me access the Profile IDs from these two tables. It does not highlight those tables nor the values from those table.

      I have StorageMode set to DirectQuery mode. Does that have anything to do with this? Why does it not allow me to use these functions as I have seen on various posts people using CALCULATETABLE and other functions inside a Calculated Column but I cannot seem to use them in my PBI Desktop File.

      Please do elaborate if you can on this.

      Many thanks once again

      Kind Regards

      • tamerj1's avatar
        tamerj1
        Community Champion
        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