Forum Discussion

NewbieJono's avatar
NewbieJono
Post Partisan
4 years ago
Solved

Mapping Table

Hello all, i have a mapping table to group different transactions.    we have a scenareo where i would like to have a duplicate mapping file with different gorupings. so ending up with two differen...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi NewbieJono ,

    Calculated columns are only calculated when you first define them and during a dataset refresh, it will not change by filters, it is a definite value, if you expect the value changes with filter, you can only use a measure.

     

    In a measure, columns cannot be referenced directly, MAX function in a measure can return the current value in the same row of the column.

     

    You can learn more about measures and calculated column in this article: calculated-measures-vs-calculated-columns

     

    And according your provided,you could  test the below steps:

    base table:

    Table:

    TableA:

    TableB:

    Relationship:

    Step1, create slicer table,and keep the below relationship:

     

    Step 2,create measure:

    refer = IF(SELECTEDVALUE(Slicer[Type])="A",LOOKUPVALUE('Table A'[kind],'Table A'[Type],MAX('Table'[TYPE])),LOOKUPVALUE(TableB[Kind],TableB[Type],MAX('Table'[TYPE])))

     

    then create visual:

    If you need to map multiple columns, you need to create multiple conditional measures one by one.

     

     

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

v-luwang-msft's avatar
v-luwang-msft
Community Support
4 years ago

Hi NewbieJono ,

Calculated columns are only calculated when you first define them and during a dataset refresh, it will not change by filters, it is a definite value, if you expect the value changes with filter, you can only use a measure.

 

In a measure, columns cannot be referenced directly, MAX function in a measure can return the current value in the same row of the column.

 

You can learn more about measures and calculated column in this article: calculated-measures-vs-calculated-columns

 

And according your provided,you could  test the below steps:

base table:

Table:

TableA:

TableB:

Relationship:

Step1, create slicer table,and keep the below relationship:

 

Step 2,create measure:

refer = IF(SELECTEDVALUE(Slicer[Type])="A",LOOKUPVALUE('Table A'[kind],'Table A'[Type],MAX('Table'[TYPE])),LOOKUPVALUE(TableB[Kind],TableB[Type],MAX('Table'[TYPE])))

 

then create visual:

If you need to map multiple columns, you need to create multiple conditional measures one by one.

 

 

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien