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

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 
Maybe you're not allowed to create a calculated column. Let me try with a measure.