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

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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

PBI_Member_01 Reasons. Not 100% sure but this has always been a common limitation with DAX columns in DirectQuery models. Measures are less limited. Always read the remarks for functions in the documentation as a lot of them have this limitation. What I posted is usually one of the last remarks if it is a limitation. These days you might consider switch a table to Hybrid storage mode and then I think it will work.

CALCULATE function (DAX) - DAX | Microsoft Docs

https://docs.microsoft.com/en-us/power-bi/desktop-composite-models