How to filter a grouped table by filtering the base table

[Anonymous]

Hello! I have a table 1 consisting of different user actions types. I need to count how freequently different users do actions in the same subsequence. 

1) I made a table 2 with unique customers and concatinated their actions in one string.
2) I made a table 3 counting unique strings in that table 2 

The problem is that I need to filter by date in the interface. When i filter dates, data in the table 1 changes, table 2 shoud be rebuilded with new concatinated actions for user who was affected by the filter. So the table 3 should be also recounted. But I dont understand how can I connect table 1 and table 2. 

[Anonymous]

Hello! 

https://app.powerbi.com/groups/me/reports/f6bdda21-518d-4f1c-9be0-616af7ac0423?ctid=21f92996-c72d-4b...
https://playground.powerbi.com/ru-ru/dev-sandbox?reportId=f6bdda21-518d-4f1c-9be0-616af7ac0423

I belive achieving my first idea is imposible, since it requires PBI to reconcatine "paths" every time you filter the date, because some actions will be filtered from users paths. With real data it seems unrealistic (>10.000 rows, i cant even concatinate it, its just loading, I'm making two sql requests).  So the best thing I came up with is filtering it backwards, clicking on the last table, then on the id in the second table to see the action with date an so on.
If you have a better idea about it, Ill be very thankful. The main idea is research dependency between dates and paths.

[Anonymous]

HI @Anonymous,

I can create a measure to show the path based on the current id group and date filter, but I can't achieve the third one.

path fromula =
IF (
    HASONEVALUE ( 'Table'[ID] ),
    CONCATENATEX ( VALUES ( 'Table'[Actions] ), [Actions], "->" )
)

CONCATENATEX function (DAX) - DAX | Microsoft Docs

Current measure expression does not work as group fields to expand other calculation aggregations. Calculation columns can be worked as groups but it not able to dynamically change based on filters. (they are working on different levels)

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous,

I think the basic filter feature should not be suitable for your requirement, you need to break the filter effect and use it as a selector instead of the filter. Then use the measure to extract the selection and apply the filter effect between different visuals.

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng