Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Exclude Product count based on selected filter

Hello Folks,

 

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

 

Product Table Data:

 

Product_IDProduct_nameColor    Processor  Ram (GB)HDD (GB)SSD (GB)Price (Rs)
100Dell XpsRedi9 7th gen325122562,00,000
101Hp OmenBlacki5 10th gen1610241281,50,000
102Hp Pavilion 15Bluei5 9th gen851212860,000
103Dell InspironGreyi3 7th gen16256055,000
104ASUS ROGBlacki5 10th gen1651225683,000
105Dell VostroGreyi9 10th gen4512049,000
106Acer AspireCharcoali7 8th gen81024059,999
107Dell AlienwareBlacki5 8th gen1620481282,99,999
108MSI GF63Greyi9 10th gen851226569,999
109Acer NitroRedi9 10th gen1625401,59,000
110Lenovo LegionBluei7 10th gen810242561,29,000

 

Exclusion Table Data:

 

Product_IDProduct_nameColorProcessorRam (GB)HDD (GB)SSD (GB)Excl_Type
104ASUS ROGBlacki5 10th gen16512256Out of Stock
108MSI GF63Greyi9 10th gen8512265Coming Soon
109Acer NitroRedi9 10th gen162540Product Discontinue
120Dell LattitudeBlacki3 5th gen82560Product Discontinue
125IBM thinkpadGreyI5 7th geb45120Out 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

Interesting Links
Need Help
Show and Tell
Tips and Tricks

1 Reply

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    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