Filtering aggregated data

[RolandsP]

Hi,

I have following table consisting of 3 columns (sorry, for ugly marking on table)

I need to do 2 level aggregation on data:

1) First, I aggregate on Customers by doing a Distinct Count of ContainerClusterID.

2) Second, I aggregate on obtained ClusterIDCount value by doing a Distinct Count of Customers (I want to know how many customers used 10 different ContainerClusterID which is 2).

I made a calculated table using such formula:

ClusterSummary =
var ClusterSummaryTable = SUMMARIZE('Data for 2 level aggregation',
'Data for 2 level aggregation'[Customer],
"ClusterIDCount", DISTINCTCOUNT('Data for 2 level aggregation'[ContainerClusterId]))
RETURN
SUMMARIZE(ClusterSummaryTable,
[ClusterIDCount],
"CustomerCount", DISTINCTCOUNT('Data for 2 level aggregation'[Customer]))

And get the result exactly I would like to get (see table below), but there is one problem I am unable to solve. I would like to be able to filter this table by date. I am unable to pass dynamic date filter (e.g. based on slicer selection) to this calculated table. 

I have read that it is not possible to pass dynamic filter to calculated tables.

Can anyone advise how could I solve this issue?

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

See if my solution here helps.

[Ashish_Mathur]

Hi,

I am not sure of how much i can help here but I'd like to try.  What is the maximum number of Cluster iD we can have per Customer?  Also, share some data in an Excel file format.

[RolandsP]

Hi Ashish,

Currently, there are 430 distinct values of Cluster ID in data set. There is no limitation on maximum number of ClusterIDs per Customer. At the moment, the maximum amount is 10.

There is some sample data: https://1drv.ms/u/s!AlWn1KJ98ETXgaBnthfrREbkdh5LJQ.

Thanks! 

[Ashish_Mathur · Accepted solution]

Hi,

See if my solution here helps.

[RolandsP · Accepted solution]

Hi Ashish,

Thanks for your input. 

Although this didn't deliver the exact solution, the approach was right. 

I just made small modifications and got the result I needed.

If you are interested, you can see the final solution here.

Thanks,

Rolands

[Ashish_Mathur]

You are welcome.