Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
It seems easy, but I am having a hard time figuring it out =/
I have some categories of products (Salmon, Mussels, Shrimp...). Some clients buy products from more than one category.
But I am interested in knowing the clients' names that DO NOT buy, for example, Shrimp (they buy only Salmon and Mussels).
I would like that info when selecting Shrimp on a slicer.
Thanks in advance.
Solved! Go to Solution.
@dpicinin does this help?
Measure =
VAR _1 =
CALCULATE (
MAXX (
FILTER ( 'Fact', 'Fact'[CAT] IN ALLSELECTED ( Slicer[CAT] ) ),
'Fact'[Cust]
),
ALLEXCEPT ( 'Fact', 'Fact'[Cust] )
)
VAR _2 =
CALCULATE ( MAX ( 'Fact'[CAT] ), FILTER ( 'Fact', NOT 'Fact'[Cust] IN { _1 } ) )
RETURN
_2
@dpicinin does this help?
Measure =
VAR _1 =
CALCULATE (
MAXX (
FILTER ( 'Fact', 'Fact'[CAT] IN ALLSELECTED ( Slicer[CAT] ) ),
'Fact'[Cust]
),
ALLEXCEPT ( 'Fact', 'Fact'[Cust] )
)
VAR _2 =
CALCULATE ( MAX ( 'Fact'[CAT] ), FILTER ( 'Fact', NOT 'Fact'[Cust] IN { _1 } ) )
RETURN
_2
Hi @dpicinin
I would recommend you set up your data model with Customer and Product dimensions.
Then you can write a measure that returns a specific value if the fact table (I'll call it Sales) is empty, and create a visual grouped by customer that filters on that measure.
I have attached an example:
Model diagram:
This is the Sales Empty measure, that returns 1 if the Sales table is empty, otherwise zero:
Sales Empty =
INT ( ISEMPTY ( Sales ) )
Report page, with the rightmost table filtered to [Sales Empty] = 1:
The Sales Empty measure is simply checking whether the Sales table is empty, so if multiple Product Category selections are made, it will return 1 for Customers that didn't purchase any of the selected Categories.
Hopefully this is useful!
Regards,
Owen
I tried to reproduce your approach @OwenAuger , but for some reason it didn't work on my model.
Then I tried @smpa01 suggestion and it worked fine. Appreciate your time guys.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |