Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
I have to create the relationship. Should it be something like this?
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?
Nope - I can remodel that aspect if needed
@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
)
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
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
)
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
Please provide sample of the expected results
Hi @Anonymous
Please provide more details along with an example
@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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |