Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create new table using another table

Hi, I am trying to groupby a table and want to create new table from that:

 


From the above dummy tableI want to create  the below table 'AggregatedTable', "NoofTest" is count of all rows coming after grouping, "PassTests" column will contain count of those row whenever TABLENAME['DIFFERENCE'] will be zero. I am getting error for the below code, how to resolve it?
AggregatedTable =

GROUPBY( TABLENAME, TABLENAME[Source], TABLENAME[KPI],

 "NoOfTests", COUNTX(CURRENTGROUP(), COUNTROWS(TABLENAME)), "PassTests",COUNTX(CALCULATETABLE(TABLENAME,TABLENAME[Difference]=0),COUNTROWS(TABLENAME)) )

Need Help
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    Hi Anonymous ，

     

    Based on the sample and description you provided, Please try code as below.

    AggregatedTable = 
            ADDCOLUMNS(
                       GROUPBY(
                       TABLENAME,
                       TABLENAME[Source], 
                       TABLENAME[KPI],
                       "NoofTest", COUNTX(CURRENTGROUP(),COUNTROWS(TABLENAME))
                    ),
                        "PassTests", COUNTX(CALCULATETABLE(TABLENAME,TABLENAME[Difference]=0),COUNTROWS(TABLENAME)) 
                    )

    Result is as below.

     

    Best Regards,
    Yulia Yan

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    Anonymous 

    what's the expected output based on the sample data you provided?

    could you pls provide a table of output as well?

     

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Icon for Community Support rankCommunity Support
    2 years ago

    Hi Anonymous ，

     

    Based on the sample and description you provided, Please try code as below.

    AggregatedTable = 
            ADDCOLUMNS(
                       GROUPBY(
                       TABLENAME,
                       TABLENAME[Source], 
                       TABLENAME[KPI],
                       "NoofTest", COUNTX(CURRENTGROUP(),COUNTROWS(TABLENAME))
                    ),
                        "PassTests", COUNTX(CALCULATETABLE(TABLENAME,TABLENAME[Difference]=0),COUNTROWS(TABLENAME)) 
                    )

    Result is as below.

     

    Best Regards,
    Yulia Yan

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      It worked! Thanks