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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

PBI_Member_01 tamerj1 Is this DirectQuery? Because if so it is probably:

  • This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

PBI_Member_01's avatar
PBI_Member_01
Helper III
3 years ago

Thank you tamerj1 for your assistance throughout this issue.

Hi Greg_Deckler ,

Yes this is DirectQuery Storage Mode and I guess you have summed it up in your message. But why is this a limitation on DirectQuery Mode for Calculated Columns? Or if you could direct me to some relevant links as I'm still a beginner and would want to know what sort of behaviour differentiates between different storage modes?

Thank you for your help on this.

Kind Regards