Exclude Product count based on selected filter

[Anonymous]

Hello Folks,

I am stuck in my Power BI visualization. Please help...

Product Table Data:

Product_ID	Product_name	Color	Processor	Ram (GB)	HDD (GB)	SSD (GB)	Price (Rs)
100	Dell Xps	Red	i9 7th gen	32	512	256	2,00,000
101	Hp Omen	Black	i5 10th gen	16	1024	128	1,50,000
102	Hp Pavilion 15	Blue	i5 9th gen	8	512	128	60,000
103	Dell Inspiron	Grey	i3 7th gen	16	256	0	55,000
104	ASUS ROG	Black	i5 10th gen	16	512	256	83,000
105	Dell Vostro	Grey	i9 10th gen	4	512	0	49,000
106	Acer Aspire	Charcoal	i7 8th gen	8	1024	0	59,999
107	Dell Alienware	Black	i5 8th gen	16	2048	128	2,99,999
108	MSI GF63	Grey	i9 10th gen	8	512	265	69,999
109	Acer Nitro	Red	i9 10th gen	16	254	0	1,59,000
110	Lenovo Legion	Blue	i7 10th gen	8	1024	256	1,29,000

Exclusion Table Data:

Product_ID	Product_name	Color	Processor	Ram (GB)	HDD (GB)	SSD (GB)	Excl_Type
104	ASUS ROG	Black	i5 10th gen	16	512	256	Out of Stock
108	MSI GF63	Grey	i9 10th gen	8	512	265	Coming Soon
109	Acer Nitro	Red	i9 10th gen	16	254	0	Product Discontinue
120	Dell Lattitude	Black	i3 5th gen	8	256	0	Product Discontinue
125	IBM thinkpad	Grey	I5 7th geb	4	512	0	Out of Stock

These are 2 tables Product table and Exclusion table, both have a primary key Product_ID, my requirement is I want to subtract the count of Products that belong to exclusion table and matching to Products table through Excl_Type selections from the count of Color and Processor.

Sample Visualization:

Filter selection:

 count of selection from (Processor and Color)=8

Excl_Type= Out of Stock and coming Soon (Count=2) because only 1 record is matching from both Excl_type on Products table.

 After clicking SUBMIT Button

Desired Output (count of Product_ID)

                         =(count from processor and count from color)(Multi selection)- count from Excl_type (Multi selection)

                         = 8-2

                         =6

NOTE: All the filters have multi selection and select all option both.

Thanks,

Abhinay

[sturlaws]

Hi @Anonymous 

 Not entirely sure what you are trying to achieve, but can you see if this measure returns what you need:

number of products =
VAR _intersection =
    INTERSECT (
        VALUES ( 'product table'[Product_ID] ),
        VALUES ( 'exclusion table'[Product_ID] )
    )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( 'product table'[Product_ID] ),
            NOT ( 'product table'[Product_ID] IN _intersection )
        )
    )

Cheers,
Sturla