Summarizing AND Logic from Slicer

[amtbew]

I have a dataset that I'm using a slicer to filter AND Logic using the dax and methodology applied from this blog post - https://community.fabric.microsoft.com/t5/Community-Blog/Apply-AND-logic-in-the-slicer/ba-p/2881583

The slicer works as intended for the table I filter by the measure suggested by the blog article, but it doesn't work on additional tables.

Here's the full data set

Here's my measure from the blog article. 

Comparison = IF (
    ISFILTERED ( 'Vendor ID Table'[Vendor ID] )
        && MAX ( 'Data'[Vendor ID] )
            IN ALLSELECTED ( 'Vendor ID Table'[Vendor ID] )
                && COUNTROWS ( 'Vendor ID Table' )
                    <= CALCULATE (
                        COUNT ( 'Data'[Vendor ID] ),
                        FILTER (
                            ALLSELECTED ( 'Data' ),
                            [Vendor ID]
                                IN ALLSELECTED ( 'Vendor ID Table'[Vendor ID] )
                                    && [Product ID] = MAX ( 'Data'[Product ID] )
                        )
                    ),
    1
)

I add the measure as a filter to a table and it works as I'd like in conjuction with a slicer. Only returns the products where both vendors have an entry (excludes P02).

Wondering how I could get the following tables to also follow the same logic and remove the P02 entry as it's not a match. The above Dax measure doesn't work as it's directly looking for the Product ID in these tables, which don't contain it. 

Here's the pbix. https://www.dropbox.com/scl/fi/gfrcvt2ewuejw070ylk3w/AndLogicSummary.pbix?rlkey=wy748oi30dho9f9sqz4n...

Thanks

[danextian]

Hi @amtbew ,

The problem with your formula is that it returns 1 only if Product ID is visible but returns 1 at the total level thus  nothing is filtered.  Try this:

Comparison 2 = 
VAR __VENDOR_COUNT =
    COUNTROWS ( 'Vendor ID Table' )
RETURN
    SUMX (
        SUMMARIZE (
            Data,
            Data[Product ID],
            "CountNo",
                CALCULATE (
                    COUNTROWS ( Data ),
                    ALLEXCEPT ( Data, Data[Product ID] ),
                    Data[Vendor ID] IN VALUES ( 'Vendor ID Table'[Vendor ID] )
                )
        ),
        IF ( [CountNo] >= __VENDOR_COUNT, 1 )
    )

Amount Filtered = 
CALCULATE ( SUM ( Data[Spend] ), FILTER ( Data, [Comparison 2] <> BLANK () ) )

[amtbew]

Hi @danextian 

It works with the sample data, however if I add a 3rd Vendor, it doesn't seem to work as intended. The slicer is not excluding the 3rd vendor.

[danextian]

Try this:

VAR __VENDOR_COUNT =
    COUNTROWS ( 'Vendor ID Table' )
RETURN
    SUMX (
        SUMMARIZE (
            Data,
            Data[Product ID],
            "CountNo",
                CALCULATE (
                    COUNTROWS ( Data ),
                    ALLEXCEPT ( Data, Data[Product ID] ),
                    Data[Vendor ID] IN VALUES ( 'Vendor ID Table'[Vendor ID] )
                )
        ),
        IF ( [CountNo] = __VENDOR_COUNT, 1 )
    )

[amtbew]

@danextian Still having trouble scaling it. Doesn't work when I add a 4th vendor. 

V01 should be excluded as it's not selected. 

Also I assume the Amount Filtered should be updated to reference comparison 3.

Here's the pbix. https://www.dropbox.com/scl/fi/gfrcvt2ewuejw070ylk3w/AndLogicSummary.pbix?rlkey=wy748oi30dho9f9sqz4n...

[danextian · Accepted solution]

I'm hoping this  one works:

Comparison 3 = 
VAR __VENDOR_COUNT =
    COUNTROWS ( 'Vendor ID Table' )
RETURN
    CALCULATE (
        SUMX (
            SUMMARIZE (
                Data,
                Data[Product ID],
                "CountNo",
                    CALCULATE (
                        DISTINCTCOUNT ( Data[Vendor ID] ),
                        ALLEXCEPT ( Data, Data[Product ID] ),
                        Data[Vendor ID] IN VALUES ( 'Vendor ID Table'[Vendor ID] )
                    )
            ),
            IF ( [CountNo] = __VENDOR_COUNT, 1 )
        ),
        FILTER ( Data, Data[Vendor ID] IN VALUES ( 'Vendor ID Table'[Vendor ID] ) )
    )

[amtbew]

@danextian this one did the trick. Thank you so much for the help.