Recursively count items using slicer values

[Anonymous]

Hi

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.

[tamerj1 · Accepted solution]

@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
    )

[Anonymous]

Hi

In Item table we have

ItemID

1

2

3

4

5

6

7

8

9

10

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

[tamerj1]

@Anonymous 
Does it have to be a disconnected table?

[Anonymous]

Nope - I can remodel that aspect if needed

[tamerj1]

@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
    )

[Anonymous]

Hi

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?

[tamerj1 · Accepted solution]

@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
    )

[Anonymous]

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?

[tamerj1 · Accepted solution]

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

[tamerj1]

@Anonymous 
Please provide sample of the expected results 

[tamerj1]

Hi @Anonymous 
Please provide more details along with an example

[amitchandak]

@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