Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Recursively count items using slicer values

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.

 

 

 

2 ACCEPTED SOLUTIONS

@Anonymous 
Hope this is what you mean

1.png

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
    )

View solution in original post

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

2.png1.png

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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

@Anonymous 
Does it have to be a disconnected table?

Anonymous
Not applicable

Nope - I can remodel that aspect if needed

@Anonymous 
This is based on a disconnected table

1.png

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
Not applicable

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?

@Anonymous 
Hope this is what you mean

1.png

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
Not applicable

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?

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

2.png1.png

@Anonymous 
Please provide sample of the expected results 

tamerj1
Super User
Super User

Hi @Anonymous 
Please provide more details along with an example

amitchandak
Super User
Super User

@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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.