And logic not working as expected in measure

[wspencer]

Link: Sample PBIX File 

Problem: I need to be able to filter the Products in the Actual table based on Category selections in the Starter table. I need to be able to use AND logic for the included categories while also being able to exclude categories. I then need to be able to create other measures using that filter logic (i.e. summing/averaging other columns in the Actual table with that same logic applied).

Example: I need to include both Categories B and C, while excluding Category D. The only product that fits that criteria is 4. I've tried to use the AND logic found here in my measure "~Selected", and it works when I concatenate the results (see the results of the "~Concat" measure).

But when I try to use it as a filter in another measure ("~FirstMeasure"), it has the exclusionary logic (i.e. it gets rid of products with D as a category) but it's missing the AND logic and returns results for both products 2 and 4.

I have tried using variations of the measure presented here as a visual filter and get the correct results, but my real dataset is millions of rows long and this becomes prohibitively slow. 

Question: Why does the concat measure work, but my filtering measure doesn't? Is there a way to fix the filtering measure? Failing that, is there a faster measure that will work as a visual filter like the one from the link above?

[sevenhills · Accepted solution]

Enhanced version, please use after the above one works only!

~SecondAttempt version17 = 
var _selProductInActual = VALUES(Actual[Product])  
var _neededProducts = Filter( 
                        EXCEPT( VALUES('Disconnected Starter'[Product]), 
                                VALUES(ExcludeTable[Product]))  
                        , [Product] in _selProductInActual)
var _neededCategories = EXCEPT(VALUES('Disconnected Starter'[Category]), 
                                VALUES(ExcludeTable[Category]))
var _neededCategoriesCount = COUNTROWS(_neededCategories) 
var _validProductsInClause = 
        SUMMARIZE( -- top level: we only need products that belongs to all categories

            FILTER  ( -- Filter those are exact count of needed categories after filtering the needed categories for each product

                    SUMMARIZE( FILTER( all(Starter), 
                                        Starter[Product] in _neededProducts 
                                        && Starter[Category] in _neededCategories
                                ), 
                                [Product], 
                                "ctg", DISTINCTCOUNT(Starter[Category]))  
                    
                    , [ctg] = _neededCategoriesCount 
            ) 

            , Starter[Product]
        )

RETURN IF(  NOT(ISFILTERED('Disconnected Starter'[Category])) 
            && NOT(ISFILTERED('ExcludeTable'[Category])),  0, 
        SUMX( FILTER(Actual, Actual[Product] IN _validProductsInClause), Actual[SomeData] )
     )
    

I added the selected products concept here and also simplified DAX ... 

[v-sgandrathi]

Hi @wspencer,

Has your issue been resolved? If the response provided by @sevenhills and @Ashish_Excel addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[v-sgandrathi]

Hi @wspencer,

Hi  

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @wspencer,

We wanted to check if you had a chance to review our last reply. Let us know if it helped or if you need more guidance—we're always happy to help further.

If the solution worked for you, please click Accept as Solution and feel free to leave a Kudos for visibility.

Looking forward to hearing from you!

[Ashish_Excel · Accepted solution]

Hi,

Download the PBI file from here.

Hope this helps.

[sevenhills]

Part A: Analysis: Few things to check before you get to final answer:
~ include ! starter categories with B and C, products are 1, 2, 3, 4

~ data to be excluded ~ category D, products are 1, 3

~ expected products in the ouput are 2 and 4. 

You are using products in the FirstMeasure, hence pointing to products here.
Assuming this is what is correct and what you want, please check other measures!

Part B: Tried this measure and it looks good with 2 and 4:

~FirstMeasure1 = 
IF(NOT(ISFILTERED(Starter[Category])) && NOT(ISFILTERED('ExcludeTable'[Category])),
0, 
 SUMX( FILTER(Actual, Actual[Product] in Except(VALUES(Starter[Product]), VALUES( ExcludeTable[Product]))), Actual[SomeData])
)

Note that, I used Except concept here and not used your other measures.

finally, I did not spend on other measures like concat, selected... 

[wspencer]

Thanks for the response. Unfortunately that's not what I need, because I need the first slicer (the included categories) to operate with AND logic.

So I need products with BOTH B and C categories, but not D. Your solution includes product 2, which only has category B. The expected result is just product 4.

[sevenhills]

Ok, this looks more harder than I thought. Spent few hours and finally got. 
Let us try this works for your real scenarios. and then we can refine it!

1. Create a "Disconnected Starter" tables, i.e., not connected to any table!
2. Use this disconnected started table for include slicer
3. Add this measure

~SecondAttempt version16 = 
var _includeProducts = VALUES('Disconnected Starter'[Product])
var _excludeProducts = VALUES(ExcludeTable[Product])
var _neededProducts = EXCEPT( _includeProducts, _excludeProducts)
-- RETURN CONCATENATEX(_neededProducts, [Product], ", ")

var _includeCategories = VALUES('Disconnected Starter'[Category])
var _excludeCategories = VALUES(ExcludeTable[Category])
var _neededCategories = EXCEPT(_includeCategories, _excludeCategories)
var _neededCategoriesCount = COUNTROWS(_neededCategories)
-- RETURN CONCATENATEX(_neededCategories, [Category], ", ")
-- RETURN _neededCategoriesCount

var _neededProducts_neededCategories =  FILTER( all(Starter), Starter[Product] in _neededProducts && Starter[Category] in _neededCategories)
var _productByCategoryCount = SUMMARIZE( _neededProducts_neededCategories, [Product], "ctg", DISTINCTCOUNT(Starter[Category])) 
var _validProducts = filter( _productByCategoryCount, [ctg] = _neededCategoriesCount  )
var _validProductsInClause = SUMMARIZE(_validProducts, Starter[Product])
RETURN -- CONCATENATEX(_validProducts, [Product], ", ")
    
    IF(  NOT(ISFILTERED('Disconnected Starter'[Category])) && NOT(ISFILTERED('ExcludeTable'[Category])),  0, 
        SUMX( FILTER(Actual, Actual[Product] IN _validProductsInClause), Actual[SomeData] )
     )
    

Note: I have added more steps for debugging at each step. Once you get the result, we can simplify by clubbing few lines of code. 

Output: tried...
For the left slicer, used disconnected starter. for the second slicer, used the external table. for the table visual, used the actual table product and the measure above!

[sevenhills · Accepted solution]

Enhanced version, please use after the above one works only!

~SecondAttempt version17 = 
var _selProductInActual = VALUES(Actual[Product])  
var _neededProducts = Filter( 
                        EXCEPT( VALUES('Disconnected Starter'[Product]), 
                                VALUES(ExcludeTable[Product]))  
                        , [Product] in _selProductInActual)
var _neededCategories = EXCEPT(VALUES('Disconnected Starter'[Category]), 
                                VALUES(ExcludeTable[Category]))
var _neededCategoriesCount = COUNTROWS(_neededCategories) 
var _validProductsInClause = 
        SUMMARIZE( -- top level: we only need products that belongs to all categories

            FILTER  ( -- Filter those are exact count of needed categories after filtering the needed categories for each product

                    SUMMARIZE( FILTER( all(Starter), 
                                        Starter[Product] in _neededProducts 
                                        && Starter[Category] in _neededCategories
                                ), 
                                [Product], 
                                "ctg", DISTINCTCOUNT(Starter[Category]))  
                    
                    , [ctg] = _neededCategoriesCount 
            ) 

            , Starter[Product]
        )

RETURN IF(  NOT(ISFILTERED('Disconnected Starter'[Category])) 
            && NOT(ISFILTERED('ExcludeTable'[Category])),  0, 
        SUMX( FILTER(Actual, Actual[Product] IN _validProductsInClause), Actual[SomeData] )
     )
    

I added the selected products concept here and also simplified DAX ...