filter table by value wich not a chosen one

[Ritaf]

Hi,

I have a table with sales data attached, i want to see potential customers to sell them a product (sku) they don't have. potential customer is one who don't have a sku or sales of a specific sku is 0.

i read a article about selected value

https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

but didnt understand how it helps me.

i hope somebody will assit me,

thanks a lot, Rita

ParentPeriod	Period	CustomerCode	CustomerName	Region	Territory	Cluster	City	CustomerStatus	SKU	Sales
2019	Jan	1000024	מכולת משה	South	יבנה	אלי כהן	רחובות	A	x	3000
2019	Feb	1000024	מכולת משה	South	יבנה	אלי כהן	רחובות	A	x	3950
2019	Mar	1000024	מכולת משה	South	יבנה	אלי כהן	רחובות	A	x	5450
2019	Apr	1000024	מכולת משה	South	יבנה	אלי כהן	רחובות	A	x	2450
2019	Jan	1000024	מכולת משה	South	יבנה	אלי כהן	רחובות	A	y	400
2019	Feb	1000024	מכולת משה	South	יבנה	אלי כהן	רחובות	A	y	250
2019	Mar	1000024	מכולת משה	South	יבנה	אלי כהן	רחובות	A	y	600
2019	Apr	1000198	מכולת בני	South	ירושלים	דוד לוי	ירושלים	A	x	6
2019	Jan	1000024	מכולת משה	North	חיפה	אלי כהן	רחובות	A	z	1450
2019	Feb	1000024	מכולת משה	North	יבנה	אלי כהן	רחובות	H	z	1750
2019	Mar	1000024	מכולת משה	North	יבנה	אלי כהן	רחובות	A	z	2300
2019	Apr	1000024	מכולת משה	North	יבנה	אלי כהן	רחובות	A	y	300
2019	Jan	1000198	מכולת בני	North	ירושלים	דוד לוי	ירושלים	A	x	20
2019	Feb	1000198	מכולת בני	North	ירושלים	דוד לוי	ירושלים	B	x	10
2019	Mar	1000198	מכולת בני	North	ירושלים	דוד לוי	ירושלים	A	x	7
2019	Apr	1000198	מכולת בני	North	ירושלים	דוד לוי	ירושלים	A	y	10

[d_gosbell · Accepted solution]

So the "trick" to doing this would be to create a disconnected table of products with a distinct list of the SKUs. In the code below I have called this table "Products to Sell" and I have called your original table "Table1" and I deleted the relationship that Power BI automatically created between the 2 SKU columns.

Then the following measure will return a 1 or a blank against each customer code if they have the selected product from the "Products To Sell" table. Because Power BI automatically hides rows that have blank values if you put just the customer code/ customer name in a table with this measure it will only list customers that have not bought the selected product.

Potential Customers = 
var _selectedSKU = ALLSELECTED('Product To Sell'[SKU])
var _selectedSales = ADDCOLUMNS( DISTINCT(Table1[CustomerCode]), "SkuSales" , CALCULATE(sum(Table1[Sales]), TREATAS(_selectedSKU, Table1[SKU])))
var _filteredSales = FILTER(_selectedSales, [SkuSales] = 0)
return COUNTROWS(_filteredSales)

[v-juanli-msft · Accepted solution]

Hi @Ritaf 

Assume you have "customer table" with "CustomerCode" column and "product table" with "product SKU" column

I can create a crossjoin table (enter this formula in formula bar (Modeling->new table)

this table would show all CustomerCode with all product SKU

crossjoin table = CROSSJOIN('customer table','product table')

Then create another table, this table would show which customer have product sku and their sales is not 0.

generate table =
SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            Sheet9,
            "sum_value", CALCULATE (
                SUM ( Sheet9[Sales] ),
                ALLEXCEPT ( Sheet9, Sheet9[CustomerCode], Sheet9[SKU] )
            )
        ),
        [sum_value] <> 0
    ),
    Sheet9[CustomerCode],
    Sheet9[SKU]
)

Finally create a new table, this table would show potential as you expected.

final Table = EXCEPT('crossjoin table','generate table')

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf]

Thanks a lot!!!

[d_gosbell · Accepted solution]

So the "trick" to doing this would be to create a disconnected table of products with a distinct list of the SKUs. In the code below I have called this table "Products to Sell" and I have called your original table "Table1" and I deleted the relationship that Power BI automatically created between the 2 SKU columns.

Then the following measure will return a 1 or a blank against each customer code if they have the selected product from the "Products To Sell" table. Because Power BI automatically hides rows that have blank values if you put just the customer code/ customer name in a table with this measure it will only list customers that have not bought the selected product.

Potential Customers = 
var _selectedSKU = ALLSELECTED('Product To Sell'[SKU])
var _selectedSales = ADDCOLUMNS( DISTINCT(Table1[CustomerCode]), "SkuSales" , CALCULATE(sum(Table1[Sales]), TREATAS(_selectedSKU, Table1[SKU])))
var _filteredSales = FILTER(_selectedSales, [SkuSales] = 0)
return COUNTROWS(_filteredSales)

[Ritaf]

Hi, and if i do need the connection between the tables, for example if i want my user click on table with

data atached and the report need show him a potential customers from region he chose?

[d_gosbell]

---

@Ritaf wrote:

Hi, and if i do need the connection between the tables, for example if i want my user click on table with

data atached and the report need show him a potential customers from region he chose?

---

If you are talking about doing this using my measure technique then you can possibly do this even if you do have a relationship. The only issue is that the table above that you are using for crossfiltering cannot have the CustomerName or CustomerCode columns that you are showing. If you can work with this restriction the following variation which uses the CROSSFILTER function to dynamically "switch off" the relationship might work for you:

Potential Customers = 
var _selectedSKU = ALLSELECTED('Product To Sell'[SKU])
var _selectedSales = CALCULATETABLE(
        ADDCOLUMNS( DISTINCT(Table1[CustomerCode]), "SkuSales" , CALCULATE(sum(Table1[Sales]), TREATAS(_selectedSKU, Table1[SKU])))
        ,CROSSFILTER(Table1[SKU], 'Product To Sell'[SKU],None),ALLEXCEPT(Table1, Table1[CustomerCode])
    )
var _filteredSales = FILTER(_selectedSales, [SkuSales] = 0)
return COUNTROWS(_filteredSales)

[Ritaf]

Thank you, i will try it.