Help on aggregation on new table

[Anonymous]

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! 

[Anonymous · Accepted solution]

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.

[PaulDBrown]

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")