Forum Discussion

KhaledEid's avatar
KhaledEid
Frequent Visitor
4 years ago
Solved

Creating table from two tables using SUMMARIZE

I am trying to create a table with three columns (Full Name, Actual Hours, Contracted Hours)

 

I created the below measure

Top Staff =
VAR K=
FILTER(HR_MonthlySalary, HR_MonthlySalary[Actual Hours] > HR_MonthlySalary[Hours Contracted])
Return
CALCULATETABLE(
SUMMARIZE(HR_Data_Final,HR_Data_Final[FullName]),
KEEPFILTERS(K)
)
but got the below error

 

Am I missing something?

 

 

 

 

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, KhaledEid ;

    According to the official document,  KEEPFILTERS(<expression>) Any expression.

     however in your dax,  "K" is return a table ,not a expression . 

    so i think you could modify it.

    Top Staff =
CALCULATETABLE (
    SUMMARIZE ( HR_Data_Final, HR_Data_Final[FullName] ),
    KEEPFILTERS ( HR_MonthlySalary[Actual Hours] > HR_MonthlySalary[Hours Contracted] )
)

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    KhaledEid , this code will return table, Seem like you are using it in a measure

     

    table ->  Top Staff =
    VAR K=
    FILTER(HR_MonthlySalary, HR_MonthlySalary[Actual Hours] > HR_MonthlySalary[Hours Contracted])
    Return
    CALCULATETABLE(
    SUMMARIZE(HR_Data_Final,HR_Data_Final[FullName]),
    K
    )

     

     

    Measure  ->

    Top Staff =
    VAR K=
    FILTER(HR_MonthlySalary, HR_MonthlySalary[Actual Hours] > HR_MonthlySalary[Hours Contracted])
    Return
    countrows( CALCULATETABLE(
    SUMMARIZE(HR_Data_Final,HR_Data_Final[FullName]),
    K
    ))

    • KhaledEid's avatar
      KhaledEid
      Frequent Visitor
      4 years ago

      thanks for your answer,
      but the table code returned the same error.  i need to create a table not measure

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, KhaledEid ;

    According to the official document,  KEEPFILTERS(<expression>) Any expression.

     however in your dax,  "K" is return a table ,not a expression . 

    so i think you could modify it.

    Top Staff =
CALCULATETABLE (
    SUMMARIZE ( HR_Data_Final, HR_Data_Final[FullName] ),
    KEEPFILTERS ( HR_MonthlySalary[Actual Hours] > HR_MonthlySalary[Hours Contracted] )
)

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.