Forum Discussion

royal20692's avatar
royal20692
Frequent Visitor
8 years ago

Capture caculated value from another table

Hi everyone, Currently, I am having this situation: I have two tables, one is the dataset (Data table) and another one is the list of KPI (KPI table) for my reports. In the first table, I created ...
royal20692's avatar
royal20692
Frequent Visitor
8 years ago

Hi Cherie,

There are no relationships between the tables since there are no common columns.

Please tell me if you have any suggestion to display the crosstab.

 

Table 1 (Measures)
Measure 1

Measure 2

Measure 3

Measure 4

 

 

Table 2 (KPI)

KPI 1

KPI 2

 

to be more specific  my exercise is to display as the followings:

                 Actual         Budget

KPI 1    | Measure 1 | Measure 2

KPI 2    | Measure 3 | Measure 4

 

So I want to create a measures like this

Actual = if( KPI="KPI 1", Measure 1, Measure 3)

Budget = if(KPI="KPI 1", Measure 2, Measure 4)

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

    Hi royal20692

     

    You may try to create measures as below:

    Actual =
IF ( SELECTEDVALUE ( table[KPI] ) = "KPI 2", [Measure 2], [Measure 4] )
Budget =
IF ( SELECTEDVALUE ( table[KPI] ) = "KPI 1", [Measure 1], [Measure 3] )

    Regards,

    Cherie

    • royal20692's avatar
      royal20692
      Frequent Visitor
      8 years ago

      Hi Cherie,

      Thank for your support,

      Actually, I want all the KPI displayed in a crosstable, so I don't know if I am going to use selectedvalue, will I have to add the KPI into my report as a filter or not.

      and also, the problem is that when I drag the created measure to the report, it cannot recognize the measure from another table.

      Please see the image below for an example