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

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

      • PBI_Member_01's avatar
        PBI_Member_01
        Helper III
        3 years ago

        Hi tamerj1 ,

        Thank you for your effort on this.

        Let me give you an idea what I want to achieve with these IDs, I want to plot these IDs on a Bar Chart visual and see how much Membership Limit (This column is Present in Table A) are these Profile IDs holding onto, since they do not exist in Table D - Order Details
        On X-Axis would be the Membership Limit Buckets and on Y-Axis would be the count for these customers present in each bucket.
        But I do require these IDs in actual format for later use based on business requirement.

        My question comes back to the very basic thing, Why am I not allowed to use the CALCULATETABLE, SUMMARIZE, EXCEPT, VALUES etc function in a calculated column.

        Also I cannot access values of Table C or Table D while creating this column.
        There is no restriction on my end as far as I know. Is this a normal behaviour or something I need to check?

        Please guide me through this if you can

        Thank you once again for your time and effort

        Kind Regards

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      PBI_Member_01 
      Maybe you're not allowed to create a calculated column. Let me try with a measure.