Create Calculated Column based on multiple values from another table

[PBI_Member_01]

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 in Table A and Table C but not in Table D (which in the above mentioned case are 5,6,7)

I also want to filter the Profile IDs in Table A by Table B 'Test_Org' = 'N' and only need to consider those Profile IDs as I traverse towards Table D.

I am a beginner Power BI user so I am not sure what would be the right approach. I have tried using RELATED function, to filter my Profile IDs by 'Test_Org' = 'N' and to my understanding, this was only successful because RELATED can be used on 1-to-many relationship with the calculated column being created on 'many' side. But If I want to go towards Table C, It doesn't highlight the table name for me.

Can someone please help me out with this or guide me on how to take this through? I have tried explaining the problem on hand but if there is anything missing, please let me know.

Thanks in advance

Regards

[tamerj1]

Hi @PBI_Member_01 
You mentioned: "So I need to create a calculated column in Table A" therefore, I assume you want to create a flag column in TableA. Please refer to attached sample file

Falg = 
VAR TestOrg = RELATED ( TableB[TEST_ORG] )
VAR AProfile = 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,
            0
        ),
        0
    )
RETURN
    Result

[PBI_Member_01]

Hi @tamerj1 ,

Thank you for your quick response on this.

I actually wanted to fetch these values (5,6,7) in a column, instead of a flag. But I think this will work as well as I can simply drag all the Profile IDs and apply a visual level filter with Flag set to 1. But for the sake of discussion, Is it possible for us to fetch only those values (5,6,7) and have them in a calculated column?

In the meantime, I am going to test this out and will update you shortly on the outcome.

Thank you so much for providing assistance on this.

Kind Regards

[tamerj1]

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

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]

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 · Accepted solution]

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

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]

@PBI_Member_01 

You don't a new column. You can slice by the original column and filter it by a measure. I already demonstrated that. 
And yes for connections your not allowed to create calculated columns as the model do not exist in your pbi file and you cannot edit a model that is used by multiple users. Have you tried creating the measure?

[PBI_Member_01]

Hi @tamerj1 ,

Thank you for your response and solution for this issue.

Just to update, I created a measure based on your proposed approach and it worked like a charm. I had to apply one or more filters based on my requirement to reach the expected outcome. Thank you so much for helping me out with this.

Just to continue on the Calculated Column discussion since I am curious about it, I am working on the PBIX File which contains the entire Data Model, and this is the main Dataset file. I am the only user working on it, Only one other person has access to this file (who rarely use it) and they only use when confirming that I am not using it and vice versa.

So my question still remains, what could be the reason why I am unable to use these functions such as CALCULATETABLE, SUMMARIZE, EXCEPT, VALUES etc when creating a calculated column, as I have seen you in your initial approach as well as other solutions posted on various topics use them frequently. Yet, I am unable to.

[tamerj1]

@PBI_Member_01 
Let me tag some of the best in the community with much more experience and information than mine.

@daXtreme @tackytechtom @johnt75 @Greg_Deckler 

[Greg_Deckler]

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

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]

@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

[tamerj1]

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

[PBI_Member_01]

Any help on this would be appreciated.

Thanks in Advance