Grouping data and identifying which records belong to each group for filtering

[Anonymous]

Hi,

My dataset is shown below:

Account_id	Transaction_id	product_code
111111	549003ABC123	001-123
111222	549003ABC123	001-123
111333	549003ABC123	001-124
111444	549123ABC789	001-789
123456	549123ABC789	001-345
234567	549002DEF001	001-001
345678	549089GHF500	001-501
543215	549089GHF500	001-501
987654	549089GHF500	001-500
876543	549089GHF500	001-500
765432	549089GHF500	001-560
654321	666999WTF005	001-005
123987	666999WTF005	001-200
567890	800812WTF456	001-456
908123	800812WTF456	001-777

I have grouped my data based on the "transaction_id" column, I have then done a distinct count on the "product_code" for each "transaction_id" group and then a count on the "account_id" that belongs to each product code after grouping by transaction_id. See below of this new table:

I can then filter this grouped table based on the distinct count "product_code" e.g. if distinct count is greater than 1. This will allow me to identify which accounts fall in this category, see below:

For the visuals I create after this, I only want to look at these specific accounts that have been identified from the grouping and filter above. How can I achieve this "on page" filter?

Do I need to create a new calculated column which assigns a value to each row that has been identified to meet this criteria? not sure how to do this, any help is appreciated. 

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Only the column can be add to page level. But it don't apply aggregation of the column value. So you need to create calculated column to use as page level filter. 

countpc = CALCULATE(DISTINCTCOUNT('Table'[product_code]),ALLEXCEPT('Table','Table'[Transaction_id])) 

Put it in page-level filter.

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

Best Regards,

Dedmon Dai