Forum Discussion

amtbew's avatar
amtbew
Helper I
2 years ago
Solved

Summarizing AND Logic from Slicer

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=wy748oi30dho9f9sqz4nfegd1&dl=0

 

Thanks

Need Help
  • danextian's avatar
    danextian
    2 years ago

    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] ) )
    )

     

6 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    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's avatar
      amtbew
      Helper I
      2 years ago

      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's avatar
        danextian
        Super User
        2 years ago

        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 )
    )