Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help on aggregation on new table

Hi,

 

I have the following table which I copied from df_Bill_Flexcab and  using the formula:

Table = SUMMARIZE(df_BillHist_Flexcab,df_BillHist_Flexcab[CIDN], df_BillHist_Flexcab[Service_Number])
 
Goal: I need to be able to aggregate the CIDN by the count of service number from the newly created table. Then I will need to group them by if Service Number>=10, ">10 services", if  Service Number<10, "Less than 10 services" and so on. 
 

Can you assist me with the formula please? Thank you! 


 

 
 
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    According to your description and screenshot , the Table is summarized by original table, so I'd suggest you add ADDCOLUMNS() to your DAX syntax like this:

    Table = 
ADDCOLUMNS (
    SUMMARIZE (
        df_BillHist_Flexcab,
        df_BillHist_Flexcab[CIDN],
        df_BillHist_Flexcab[Service_Number]
    ),
    
    "Flag",
        VAR _count =
            CALCULATE (
                DISTINCTCOUNT ( df_BillHist_Flexcab[Service_Number] ),
                ALLEXCEPT ( df_BillHist_Flexcab, df_BillHist_Flexcab[CIDN] )
            ) + 0
        RETURN
            IF ( _count < 10, "Less than 10 services", ">10 services" )
)

    The final output is shown below:

     

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

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Try:

    Count of Service number = CALCULATE(COUNT(table[service number]), ALLEXCEPT(Table, Table [CIDN]))

     

     

    and then

    Group = SWITCH(TRUE(),
    [Count of service number] >= 10, ">10 services",
    "Less than 10 services")

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    According to your description and screenshot , the Table is summarized by original table, so I'd suggest you add ADDCOLUMNS() to your DAX syntax like this:

    Table = 
ADDCOLUMNS (
    SUMMARIZE (
        df_BillHist_Flexcab,
        df_BillHist_Flexcab[CIDN],
        df_BillHist_Flexcab[Service_Number]
    ),
    
    "Flag",
        VAR _count =
            CALCULATE (
                DISTINCTCOUNT ( df_BillHist_Flexcab[Service_Number] ),
                ALLEXCEPT ( df_BillHist_Flexcab, df_BillHist_Flexcab[CIDN] )
            ) + 0
        RETURN
            IF ( _count < 10, "Less than 10 services", ">10 services" )
)

    The final output is shown below:

     

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