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.
Fairly simplified I have two tables, Customers (1 column, CustomerID) and CustomerProducts (2 columns, CustomerID and ProductID). These are related by CustomerID, so that selecting a product on a slicer on ProductID shows the customers having that product.
But how can I invert it, so that selecting a ProductID shows the customers who does not have that product?
Regards,
John Martin
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file whether it suits your requirement.
In the sample, I created disconnected table in order to create product slicer, and the below measure in order to put it in "Filters on this visual"
Flag measure: =
VAR _productselectlist =
DISTINCT ( 'Product'[Product] )
VAR _customerlist =
SUMMARIZE (
FILTER ( CustomerProduct, CustomerProduct[Product] IN _productselectlist ),
Customer[Customer]
)
VAR _notcustomerlist =
EXCEPT ( ALL ( Customer[Customer] ), _customerlist )
RETURN
IF (
COUNTROWS ( FILTER ( Customer, Customer[Customer] IN _notcustomerlist ) ) > 0,
1
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi John,
I took some more time to verify my approach and it should definetely work.
First column are actual sales and the second column is my measure approach for the NOT filter.
As soon as I take out the first measure only the NOT measure is left and only the customers without the product come up
I even used it on a little bit more complex case with the following data model:
Here is the formula I used in the model above:
Relationship Function | CROSSFILTER | Customers not having a selected product =
CALCULATE(
IF(COUNTROWS(DimProduct)>0, BLANK(),1),
CROSSFILTER(FactOnlineSales[ProductKey],DimProduct[ProductKey],BOTH)
)
I used the CROSSFILTER function to make sure that the filter context starting from Product also goes through the relationship between Fact table and Customer.
Best regards
Michael
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
AS in the solution from Jihwan you can also put the measure in the visual filter
Best regards
Michael
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
Hi Michael,
This is a screenshot, as you see it a mix of english and norwegian (ok, you probably didn't recognize that 🙂 )
Quick glossary:
kundenr = CustomerID
SkadeforsikringsavtaleID = contractID
Produktnavn = productID
Premiebelop = amount
So the simple goal is to be able to slice on products to show customers who do NOT have the selected product(s).
Regards,
John Martin
AS in the solution from Jihwan you can also put the measure in the visual filter
Best regards
Michael
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
Thanks, that solved the problem. But then I try to step up a bit:
I replace the product-table with a deal-table, consisting of customerID, contractID, productID and amount.
The relationsship between customers and deals are one-to-many on customerID, a customer can have any number of deals.
Instead of slicing on the whole producttable, I now create a slicer on products from the deal-table. How do I make the slicer work so it shows only customers not having the selected product? I set up two table-visuals, one containg customers-table, the other deals-table. When I show all the deals, and adding your measure to the customers-visual, I get all the customers that does not have a deal (and hence none of the products), but as soon as I try to slice on products, it doesn't show any customers at all. Do you have any suggestion for this challenge?
Regards,
John Martin
Hi @jmkvalsund,
Thank you for your feedback! 🙂
To make sure that I do not lose track and give feedback on wrong assumptions could you please share a picture of the current data model (only the relevant tables incl. relations), the measure you now use as well as the visual you want to create?
Best regards
Michael
Hi Michael,
This is a screenshot, as you see it a mix of english and norwegian (ok, you probably didn't recognize that 🙂 )
Quick glossary:
kundenr = CustomerID
SkadeforsikringsavtaleID = contractID
Produktnavn = productID
Premiebelop = amount
So the simple goal is to be able to slice on products to show customers who do NOT have the selected product(s).
Regards,
John Martin
Hi,
Please check the below picture and the attached pbix file whether it suits your requirement.
In the sample, I created disconnected table in order to create product slicer, and the below measure in order to put it in "Filters on this visual"
Flag measure: =
VAR _productselectlist =
DISTINCT ( 'Product'[Product] )
VAR _customerlist =
SUMMARIZE (
FILTER ( CustomerProduct, CustomerProduct[Product] IN _productselectlist ),
Customer[Customer]
)
VAR _notcustomerlist =
EXCEPT ( ALL ( Customer[Customer] ), _customerlist )
RETURN
IF (
COUNTROWS ( FILTER ( Customer, Customer[Customer] IN _notcustomerlist ) ) > 0,
1
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks a lot, this solved my case 🙂
Hi John,
Lets say you put the the customer column into a matrix and the product column in a slicer then this measure should do it when you also put it into the matrix.
Customers not having the product = IF(COUNTROWS('Customer Table') > 0 , BLANK(), 1)
I have the assumption that the product table filters the customer table. (filter direction). IF thats not the case please try something like this.
Customers not having the product =
CALCULATE(
IF(COUNTROWS('Customer Table') > 0 , BLANK(), 1),
CROSSFILTER(ProductTable[CustomerColumn], ProductTable[CustomerColumn], BOTH)
)
Best regards
Michael
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
Hi John,
I took some more time to verify my approach and it should definetely work.
First column are actual sales and the second column is my measure approach for the NOT filter.
As soon as I take out the first measure only the NOT measure is left and only the customers without the product come up
I even used it on a little bit more complex case with the following data model:
Here is the formula I used in the model above:
Relationship Function | CROSSFILTER | Customers not having a selected product =
CALCULATE(
IF(COUNTROWS(DimProduct)>0, BLANK(),1),
CROSSFILTER(FactOnlineSales[ProductKey],DimProduct[ProductKey],BOTH)
)
I used the CROSSFILTER function to make sure that the filter context starting from Product also goes through the relationship between Fact table and Customer.
Best regards
Michael
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |