Donut chart with 'Other Category' and date slicer

[Puthenveedu]

Hello, 

I have a table (CategoryDetails table)  with 'Category,  ID , DateCreated. One ID can have mulitple 'Category' and one DateCreated.

CategoryA , ID1 , 2018/01/05

CategoryB , ID1 , 2018/01/05

CategoryC , ID1 , 2018/01/05

CategoryA , ID2 , 2018/02/22

Category B , ID2 , 2018/02/22 etc

I want to create a donut chart with only top 5 'Category' and the rest of the 'Category' falls under "Other Category" and there is a DateSlicer with 'DateCreated' . 

If I create a calculated table with SUMMARIZE to group by 'Category' and count the IDs and linked the 'Category' to the original table , the count seems to be static and not responding to the date slicer from the 'CategoryDetails' table.

=SUMMARIZE( ALLSELECTED(CategoryDetails) , CategoryDetails[Category] ,

 "Count", CALCULATE( COUNT(CategoryDetails[ID]) , ALLSELECTED( CategoryDetails[DateCreated]))

)

Another approach I took was to create 3 measure , one to Count the IDs and then to  RANKX  based on the Count and then third measure to rename the title to 'Other Category' if the rank is more than 5 , This works the way I want in table format but I cant assign the newly created measure(SudoCategory) as a legend to the Donut chart.

Category      |          Count      |  Rank   |               SudoCategory

Category D    ,      150         ,     1           ,  Category D

Category M    ,      145        ,     2           ,  Category M

Category R    ,      142         ,     3           ,  Category R

Category C    ,      100         ,     4           ,  Category C

Category K    ,        99         ,     5           ,  Category K

Category E    ,        97         ,     6           ,  'Other Category"

Category F    ,        90         ,     7           ,  'Other Category"

Please let me know if there is another way or if my SUMMARIZE formula can be updated somehow.

thanks

[Puthenveedu · Accepted solution]

Managed to find the solution...INTERSECT is the key to fooling the donut chart when its hesitating to take dax column from the union.

https://www.minceddata.info/2018/06/06/topsomething-and-all-the-rest-called-other/

top 5 and other =

var top5 = CALCULATETABLE(TOPN(5,VALUES(Table1[Month]),CALCULATE(SUM('Table1'[Amount]))))

var other = ROW("Month", "Other")

var allTheRest = CALCULATE(SUM(Table1[Amount]), EXCEPT(VALUES('Table1'[Month]),top5))

var theUnion = UNION(top5,other)

return

SUMX(

    INTERSECT('Unrelated',theUnion)

    ,

    var currentIterator = 'Unrelated'[Month]

    return

    IF(

        'Unrelated'[Month] <> "Other"   

        ,CALCULATE(

            SUM('Table1'[Amount])

            ,'Table1'[Month] = currentIterator

        )

        ,allTheRest

    )

)