Forum Discussion

1234Chris's avatar
1234Chris
Regular Visitor
1 year ago
Solved

Help with selectcolums / relatedtable / filter

I've read for a while now but keep going around in circles to what seems a simple problem and probably is, so sorry for my simple mind not working.

 

Master Table with unique customer referrence 'UCR' column  (and a few others but not important maybe)

 

Customer Table with unique customer referrence UCR , NAME, Text (3 Columns only)

 

Customer Table many to one relationship with Master Table.

 

In the Master Table I want to add a column from Customer Table where the UCR match, filtered to display data from the Text Column only if the Name matches a specific text that I can select.

 

 

So I tried : 'aaa' = FILTER(RELATEDTABLE(Customer Table),Customer Table[NAME] = aaa)

 

But get error.... multiple columns .... scalar vaule

Selectcolums relatedtable filter
  • ahmetyilmaz's avatar
    ahmetyilmaz
    1 year ago

    Hi, below are the measures I created for Master,

     

    aaa = LOOKUPVALUE(customer_table[Text],
                      customer_table[UCR], SELECTEDVALUE(master_table[UCR]),
                      customer_table[Name], "aaa")
     
    bbb = LOOKUPVALUE(customer_table[Text],
                      customer_table[UCR], SUM(master_table[UCR]),
                      customer_table[Name], "bbb")
     
    ccc = LOOKUPVALUE(customer_table[Text],
                      customer_table[UCR], SELECTEDVALUE(master_table[UCR]),
                      customer_table[Name], "ccc")

     

    Remember to select this to also display empty rows.

     

     

     

3 Replies

  • ahmetyilmaz's avatar
    ahmetyilmaz
    Icon for Advocate II rankAdvocate II
    1 year ago

    Hi, below are the measures I created for Master,

     

    aaa = LOOKUPVALUE(customer_table[Text],
                      customer_table[UCR], SELECTEDVALUE(master_table[UCR]),
                      customer_table[Name], "aaa")
     
    bbb = LOOKUPVALUE(customer_table[Text],
                      customer_table[UCR], SUM(master_table[UCR]),
                      customer_table[Name], "bbb")
     
    ccc = LOOKUPVALUE(customer_table[Text],
                      customer_table[UCR], SELECTEDVALUE(master_table[UCR]),
                      customer_table[Name], "ccc")

     

    Remember to select this to also display empty rows.

     

     

     
    • 1234Chris's avatar
      1234Chris
      Regular Visitor
      1 year ago

      Hi, just trying to do the same but rather than a measure have the output as a new column ??? no error is displayed if I create a new column using your reply however also no information displayed in the column ?? hate to ask again but if you are able to assist?

       

      I have managed to use Lookupvalue but by adding a column to Customer Table from Master Table .. guess due to Customer Table having UCR repeated and UCR is uniquie in Master Table. However Ideally I would need to add columns into the Master Table ???   In this direction Lookupvalue did not require selectedvalue . so going to keep trying.

       

      Found it 🙂   SUMX(Filter ........    

  • 1234Chris's avatar
    1234Chris
    Regular Visitor
    1 year ago

    Hi Ahmetyilmaz, thank you for your assistant, this has worked.