Forum Discussion
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-th...
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] ) ) )
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.
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 )
)
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=wy748oi30dho9f9sqz4nfegd1&dl=0
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] ) ) )