topic Re: Recursively count items using slicer values in DAX Commands and Tips

Recursively count items using slicer values

Anonymous — Fri, 10 Mar 2023 16:42:07 GMT

I have a disconnected table :

Items : ItemName

And a fact table :

CustomerItems : ItemName, CustomerName

I need to filter the fact table in a recursive way i.e. if the user selects in the slicer 1 item - this is easy to filter the fact with TREAT AS.

If the user select 2 items from the slicer - I need to count the customers from CustomerItems[CustomerName] by first applying the filer from the the first item in the slicer followed by the second item. i.e. the first set will provide set 1 but the second item should filter this set 1 rather than the original fact.

Assume there is 4 maxium selection from the slicer.

I don't think this is possible without using seperate virtual tables but the user may click on 2 items from slicer.

Any tips how to build that measure? I'm not welded to using a slicer to capture Items.

Re: Recursively count items using slicer values

amitchandak — Sat, 11 Mar 2023 01:40:58 GMT

Anonymous , it you need

Item 1, count(Customer)

item2 , count(Customer)

Then it is simple distinctcount(Table[Customer])

if you need liek

Item 1 - Count

Item 1, Item 2 - Count

Then consider window function in distinctcount(Table[Customer])

calculate( distinctcount(Table[Customer]) , window(1,abs,0.rel, allselected(Table[item]) ,ORDERBY(Table[item])))

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Re: Recursively count items using slicer values

tamerj1 — Sat, 11 Mar 2023 04:08:21 GMT

Hi Anonymous
Please provide more details along with an example

Re: Recursively count items using slicer values

Anonymous — Mon, 13 Mar 2023 13:10:46 GMT

In Item table we have

ItemID

In CustomerItem Table we have

CustomerID ItemID

1 1

1 2

1 3

2 1

2 3

2 4

3 1

3 2

3 3

4 1

4 3

So when user selects item 1 in the slicer the count is 3 customers and we need the display customers below,

Customer ID Item ID

1 1

2 1

3 1

When the user adds to to the selection i.e. item 1 and item 2

We need to re-calcuate using the customer ID's found from the first calculation ie. 1, 2, 3 and re-calculate the count / filter based on item 2

i.e.

Customer ID ItemID

1 2

3 2

Customer Count is now 2

I'm not sure if we can do this recursive calculation with Window functions unless I duplicate the fact for each item selectable which isn't really an option as this data in CustomerItem is rougly 50m rows

Nelly

Re: Recursively count items using slicer values

tamerj1 — Mon, 13 Mar 2023 12:49:55 GMT

Anonymous
Does it have to be a disconnected table?

Re: Recursively count items using slicer values

Anonymous — Mon, 13 Mar 2023 13:04:50 GMT

Nope - I can remodel that aspect if needed

Re: Recursively count items using slicer values

tamerj1 — Mon, 13 Mar 2023 13:46:45 GMT

Anonymous
This is based on a disconnected table

Count = VAR SelectedItems = COUNTROWS ( VALUES ( 'Item'[ItemID] ) ) VAR ItemCount = SUMX ( VALUES ( 'Item'[ItemID] ), COUNTROWS ( FILTER ( CustomerItem, CustomerItem[ID] = 'Item'[ItemID] ) ) ) RETURN IF ( SelectedItems > 1, IF ( ItemCount > 1, ItemCount ), ItemCount )

Re: Recursively count items using slicer values

Anonymous — Mon, 13 Mar 2023 15:17:33 GMT

I need to add the Item ID to the table visual too - this breaks the measure unfortunately. Any ideas how I can amend the measure to display Item ID in the visual along with Customer ID?

Re: Recursively count items using slicer values

tamerj1 — Mon, 13 Mar 2023 15:21:50 GMT

Anonymous
Please provide sample of the expected results

Re: Recursively count items using slicer values

tamerj1 — Tue, 14 Mar 2023 04:46:04 GMT

Anonymous
Hope this is what you mean

Count = VAR SelectedItems = COUNTROWS ( VALUES ( 'Item'[ItemID] ) ) VAR ItemCountCheck = SUMX ( VALUES ( 'Item'[ItemID] ), COUNTROWS ( FILTER ( CALCULATETABLE ( CustomerItem, ALL ( CustomerItem[ID] ) ), CustomerItem[ID] = 'Item'[ItemID] ) ) ) VAR ItemCount = SUMX ( VALUES ( 'Item'[ItemID] ), COUNTROWS ( FILTER ( CustomerItem, CustomerItem[ID] = 'Item'[ItemID] ) ) ) RETURN IF ( SelectedItems > 1, IF ( ItemCountCheck > 1, ItemCount ), ItemCount )

Re: Recursively count items using slicer values

Anonymous — Tue, 14 Mar 2023 14:25:14 GMT

Is it possible to amend this calculation to use in a card visual that returns the distinct count of customerID identified in the last measure?

Re: Recursively count items using slicer values

tamerj1 — Wed, 15 Mar 2023 04:47:27 GMT

Anonymous
I have to create the relationship. Should it be something like this?