Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I have this table:
And I have the slicer below where I chose 2 products out of 3.
What I want to see is the number of names who only bought the products in my slicer, and nothing else.
I just want the number of names as shown in blue. (actual names inside the parenthesis "( )" are not required).
Explanation of wished measure:
Apple = 1 / because it was only Sam who has only apple under his name;
Banana = 0 / because there is no person who has only banana;
Total = 2 / because I am asking a question: How many people has bought the products shown in my slicer, and nothing else.
so = 2 means Sam (only apple) and Anna (apple and banana)
"Alex" is not in the list because he has purchased a product which is not chosen in my slicer.
I hope i was clear with explanation.
Solved! Go to Solution.
You can create a measure like
Bought only these products =
VAR ChosenProducts = VALUES( 'Sales'[Product] )
VAR OtherProducts =
EXCEPT( ALL( 'Sales'[Product] ), ChosenProducts )
VAR BoughtChosenProducts =
CALCULATETABLE( VALUES( 'Sales'[Name] ), ChosenProducts )
VAR BoughtOtherProducts =
CALCULATETABLE( VALUES( 'Sales'[Name] ), OtherProducts )
VAR Result =
COUNTROWS(
EXCEPT( BoughtChosenProducts, BoughtOtherProducts )
)
RETURN
COALESCE( Result, 0 )
You can create a measure like
Bought only these products =
VAR ChosenProducts = VALUES( 'Sales'[Product] )
VAR OtherProducts =
EXCEPT( ALL( 'Sales'[Product] ), ChosenProducts )
VAR BoughtChosenProducts =
CALCULATETABLE( VALUES( 'Sales'[Name] ), ChosenProducts )
VAR BoughtOtherProducts =
CALCULATETABLE( VALUES( 'Sales'[Name] ), OtherProducts )
VAR Result =
COUNTROWS(
EXCEPT( BoughtChosenProducts, BoughtOtherProducts )
)
RETURN
COALESCE( Result, 0 )
WOW,
it works as wished.
New issue i didnot think of - speed of the measure. It takes long time when applied on a table with many rows.
Is diving your code into two measures would be a good solution? Or what would you recommend?
It depends on how dynamic you need it to be. If you need to be able to slice by date period or some other variable then I'm not sure how it could be speeded up. However, if you only need to consider the entire sales table you could create a calculated table containing all the customers and all the products they have ever bought, e.g.
Customers Products =
GENERATE (
ALLNOBLANKROW ( 'Sales'[Name] ),
CALCULATETABLE ( VALUES ( 'Sales'[Product] ) )
)
You could then change my original code to work from this table instead of the Sales table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |