Forum Discussion

apolotakeshi's avatar
apolotakeshi
Frequent Visitor
8 years ago

Using switch to dinamically create a dimension (need list not values)

Hi all I have a Table4 in which I select the value that I want, it is either 1 or 2.

if it is 1, i want the column to show the values that exists in Table_01 column Dbase17, else I want them to return the Table_02 data.

 

how can i do it?


here is what I have so far:

Dinamic_Column =
if(
SELECTEDVALUE(Table4[Value])="1"
;RELATED(Table_01[Dbase17])
;RELATED(Table_02[Dbase16])
)

also tried:
SWITCH(FIRSTNONBLANK('Table4'[Value];1)
;"1"; RELATED(Table_01[Dbase17])
;"2"; RELATED(Table_02[Dbase16])
;"xx")

None of them worked.
PS: the tables are connected, I have a field that connects Dbase17 with my Dbase and Dbase to Dbase16

Need Help

6 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi apolotakeshi,

    Please create a calculated column using the formula below in Table4.

    Dinamic_Column =
if(Table4[Value]="1"
;RELATED(Table_01[Dbase17])
;RELATED(Table_02[Dbase16])
)


    We assume the column including either1 or 2 named as "column1". Then create a table visual, select the Table4[column1] and Dinamic_Column as value level. Create a slicer including Table4[column1], the visual will change as you select different value in slicer.

    Best Regards,
    Angelia

    • apolotakeshi's avatar
      apolotakeshi
      Frequent Visitor
      8 years ago

      Hi v-huizhn-msft!! I got the following error:

      "The column 'Table_01[Dbase16]' either doesn't exist or doesn't have a relationship to any table available in the current contex."

      here is a sample of the tables, I will only try to use Table_01 for now, but is the same structure for table_02

      the possible values for field Value in table4 are 1,2.

      Dinamic_column is the one i'm trying to archieve.

       

       

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi @apolotakesh,

        From the screenshot, there is relationship between Table_01 and Table4, what's the field used to relate the two tables? Do you mind share your .pbix file? You can share it by private message.

        Best Regards,
        Angelia

    • CerbPBI's avatar
      CerbPBI
      Frequent Visitor
      4 years ago

      Related works if there is one to many relationship. From table 4 to table1 , you have many to one.

      create the above calculated column on table 1 instead.

       

      Dynamic_Column =
if(Trelaed(table4[Value])="1"
;Dbase17
;Dbase16)
)