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 a measure to calculate the value of the KPI for the selected period, based on my filter. (Turnover CP)

In the second table, I created a measure (Actual) to get the calculated value using the KPI name.

However, the result returns for me is blank since the KPI couldn't get the value

 

The DAX for the measures are the following:

 

Turnover CP =  CALCULATE(AVERAGE('HR Data'[Employee Turnover]),FILTER(ALL('HR Data'[MonthStart]),'HR Data'[MonthStart]=DATE(SELECTEDVALUE('Date'[Year]),SELECTEDVALUE('Date'[Month]),1)))

 

Actual = IF([KPI]="Turnover Rate",[Turnover CP],0)

 

Thank you in advance.

4 Replies

    • 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