Forum Discussion

mq2020's avatar
mq2020
Icon for Helper III rankHelper III
6 years ago
Solved

Create calculated column from a calculation in a different table

Hi!   I have a table (Table 1) with organisations and another table (Table 2) with users per organisation and whether they are claimin or not. I want to add a calculated column in Table 1 (in blue...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi mq2020 ,

    Please try to create the following measure to get the flag of every organization with claim:

    Org Claiming YN = 
VAR sumofClaimU =
    CALCULATE ( DISTINCTCOUNT ( 'User'[User] ), 'User'[User Claimed Y/N] = "y" )
RETURN
    IF ( sumofClaimU > 0, "Y", "N" )

    Best Regards

    Rena

mq2020's avatar
mq2020
Icon for Helper III rankHelper III
6 years ago

Hi again,

 

So I think I found a way to add that extra column however when I try to use IF to do the classification it's giving me a "circular dependancy error" - any idea how to get around this?

 

I first created a column in table Users to change Y N to 0 and 1 (Just so i can then sum this in the next step)

 

Users Claiming 1/0 = if(User[User Claimed Y/N]="Y",1,0)
 
Then I created a column in Organisations table to calculate the sum of users claiming using the following formula, which seems to be working as expected:
 
Claiming Users = CALCULATE(sum(User[Users Claiming 1/0]),USERELATIONSHIP(Organisations[Organisation ID],User[Organisation ID]))
 
My last step is to add an extra column in the Table Organisations to classify each organistion into groups depending on the value of the calcualted column above, however when I do this simple IF statement I get a circular dependency error - not sure how to get around this.
 
 
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi mq2020 ,

Please try to create the following measure to get the flag of every organization with claim:

Org Claiming YN = 
VAR sumofClaimU =
    CALCULATE ( DISTINCTCOUNT ( 'User'[User] ), 'User'[User Claimed Y/N] = "y" )
RETURN
    IF ( sumofClaimU > 0, "Y", "N" )

Best Regards

Rena

  • mq2020's avatar
    mq2020
    Icon for Helper III rankHelper III
    6 years ago

    Thank you so much Anonymous !

    I actually applied the same to a calculated column istead of measure so that I could use the column to split my line chart using the Legend Box and use the grouping in slicers which the measure wasnt allowing me to do.