Forum Discussion
Create Calculated Column based on multiple values from another table
PBI_Member_01
Please try the following measureFalg1 = 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
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
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
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 RegardsYou 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?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.