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