Still issues with making a slicer an "and" rather than an "or"

[datadelta]

I'm trying to return only those that have multiple selected items.  It will show those but it also brings back any that have one or the other.  Any ideas?  Here's my formula

All categories =

var numofselectedcategories=COUNTROWS(VALUES('ProductList'[product]))  

VAR Ratings=

SUMMARIZE(ProductList, ProductList[Custid], 'ProductList'[product])

var multipair=

GROUPBY(Ratings,

ProductList[product],"@rate",SUMX(CURRENTGROUP(),1))

var SameImpair=

FILTER(multiimpair,[@rate]=numofselectedproduct)

 RETURN

 COUNTROWS(product)

[v-xinruzhu-msft]

Hi @datadelta 

You can try the following.

All categories =
VAR numofselectedcategories =
    COUNTROWS ( VALUES ( 'ProductList'[product] ) )
VAR Ratings =
    SUMMARIZE (
        ALLSELECTED ( 'ProductList' ),
        'ProductList'[Custid],
        "Counts", COUNTROWS ( 'ProductList'[product] )
    )
VAR c =
    ADDCOLUMNS ( Ratings, "Flag", IF ( [Counts] = numofselectedcategories, 1, 0 ) )
RETURN
    SUMX ( c, [Flag] )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[datadelta]

Thank you.  Unfortunately this still brings back all results for the items, not just the ones who have the choices made in the selector.  This is also doing a count and I'm trying to use the slicer to only return those results that have either the single item selected or the multiple selections.  Thanks for the possible solution though!

[v-xinruzhu-msft]

Hi @datadelta 

Thanks for your quick reply, can you provide some sample data and the output you want so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

[datadelta]

I cannot share the data as it is very sensitive information but here's the formula using my data points.  The CaseID is the delta that all information is tied to.  The "impairment" is the item being used in the slicer to choose multiples.  I'm trying to get the "and" function to return the CaseID and "rateclass" associated with any CaseID's that have those impairments.  

I hope that helps and I appreciate the assistance.

All categories =

var numofselectedcategories=COUNTROWS(VALUES(Impairment[Impairment]))  

VAR Ratings=

SUMMARIZE(Impairment, Impairment[Impairment], Impairment[CaseId])

var multipair=

GROUPBY(Impairment,

Impairment[CaseId],"@rate",SUMX(CURRENTGROUP(),1))

var SameImpair=

FILTER(multipair,[@rate]=numofselectedcategories)

 RETURN

 (System[Impairment])

[v-xinruzhu-msft]

Hi @datadelta 

Thanks for your quick reply, based on your code, did you create it as a measure instead of calculated column, and you may be misleading me about what I mean, you don't need your real data, can you create some sample data based on your needs, because based on formulas alone can't guarantee that the proposed solution will meet your expectations.

Best Regards!

Yolo Zhu

[datadelta]

I did have it as a measure so I updated to calculated column.  No luck.

Here's the redacted data.  I put the items that have results associated with both "impairments" Thanks for your help!

CaseId	Submitted To	Impairments	RateClass
59825	Company1	Impairment1	OfferY
59825	Company2	Impairment1	OfferY
59825	Company3	Impairment1	OfferY
59825	Company4	Impairment1	OfferY
59825	Company5	Impairment1	OfferY
59825	Company6	Impairment1	OfferY
60054	Company 7	Impairment1	OfferY
60054	Company 7	Impairment2	OfferN
60289	Company8	Impairment2	OfferN
60289	Company9	Impairment2	OfferN
60289	Company2	Impairment2	OfferY
60289	Company2	Impairment1	OfferY
60293	Company5	Impairment1	OfferN
60293	Company5	Impairment2	OfferN

[v-xinruzhu-msft · Accepted solution]

Hi @datadelta 

Thanks for your quick reply, you can try the folllwing measure.

MEASURE =
VAR numofselectedcategories =
    COUNTROWS ( ALLSELECTED ( 'Impairment'[Impairments] ) )
VAR Ratings =
    SUMMARIZE (
        ALLSELECTED ( 'Impairment' ),
        'Impairment'[CaseId],
        [Submitted To],
        "Counts_impairments", DISTINCTCOUNT ( Impairment[Impairments] )
    )
VAR _Counts_submittedto =
    CALCULATE (
        DISTINCTCOUNT ( Impairment[Submitted To] ),
        ALLSELECTED ( Impairment ),
        Impairment[CaseId] IN VALUES ( Impairment[CaseId] )
    )
VAR _add =
    ADDCOLUMNS (
        Ratings,
        "Flag", IF ( [Counts_impairments] = numofselectedcategories, 1, 0 )
    )
VAR _sumflag =
    SUMX ( FILTER ( _add, [CaseId] IN VALUES ( Impairment[CaseId] ) ), [Flag] )
RETURN
    IF ( _sumflag = _Counts_submittedto, 1, 0 )

Then put the measure to the visual filter.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[datadelta]

This worked!  I cannot thank you enough for sticking with me on this.  I've been trying to solve this for several days and your help made it possible!

Thank you!

[Shravan133]

Hi,

try this:

All categories =
VAR NumOfSelectedCategories = COUNTROWS(VALUES('ProductList'[product]))
VAR CustProducts =
SUMMARIZE(
ProductList,
ProductList[Custid],
"NumProducts", COUNTROWS(VALUES(ProductList[product]))
)
VAR FilteredCustProducts =
FILTER(
CustProducts,
[NumProducts] = NumOfSelectedCategories
)
RETURN
COUNTROWS(FilteredCustProducts)

[datadelta]

Thank you.  Unfortunately this still brings back all results for the items, not just the ones who have the choices made in the selector.  This is also doing a count and I'm trying to use the slicer to only return those results that have either the single item selected or the multiple selections.  Thanks for the possible solution though!