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 Anonymous 

 

These are the tables and relationships:

 

Sample data of the 3 tables:

 

Sales -> 

 

Organisations -> 

 

Users -> 

 

At the moment I am displaying Av/units per organisation (a measure in the model) over time (visual below).

What I'd like to do is split this into 2 groups (organisations with users claiming vs organisations with users not claiming). 

When I try adding this field (User Claimed Y/N) to the model (in the legend part of the visual), it's not doing what I expect.

 

 

What I expect is the following (which I've done in excel to demonstrate)

 

 
parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

mq2020 It is a bit of confusing model, what happens if users under an organization have both yes and no, which bucket it will fall or this will never happen? Yes/No it is at a user level and you are viewing the data at org level, so you need to clarify your business rules.

 

There are many ways to do it but it will depend on your answer above.

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

    Hi parry2k 

     

    If 0 users claim-> Organisation Claiming No

    If 1 or more than one users claim - Organisation Claiming Yes

     

    Initially I just wanted to fix it my adding a column to the table Organisations to count the number of users claiming for each organisation, then I can add an extra column to do the classification with IF.

     

    It is the count of users claiming that i dont know how to add in the Organisations table - if I could get help with this then that should solve the issue I believe. Thank you so much

  • 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.