Dax to Filter combinations of values

[Anonymous]

Hi Community,

Thanks for helping me earlier today. But now I am stuck with a new issue. In the following snapshot, I want to show invoices with only any combinations of (23 and 44 or 200 or 201 or 203)  OR (36 and 44 or 200 or 201 or 203) service codes, so the red crossed invoices should not appear in the matrix:  Sample PBi is attached.

My sample pbi is attached:

https://1drv.ms/u/s!Ag919_pO_UKrgQLIOrUlBri_AeVY?e=6nDoTc

Thanks in advance!

Meena

[Greg_Deckler]

You should be able to implement a variation of my Inverse Selector quick measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290#M325

Just construct your measure so that it meets your logic.

[Anonymous]

Thanks @Greg_Deckler , Your inverse selection measure is great. But it doesnt give me the combination pattern I am after..

[Greg_Deckler]

Correct, you need to adjust the measure so that it implements your selection logic. If you can provide sample data I can probably whip it together for you. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@Greg_Deckler here is my sample PBI:

https://1drv.ms/u/s!Ag919_pO_UKrgQLIOrUlBri_AeVY?e=ZRG2S6

[Greg_Deckler]

Hey @Anonymous , I have this almost working the way I want. I have it so that it at least shows the right invoiveid's. However, I need to tweak it to get it to display all of the GP Codes for those invoiceids. Need to think about it a bit but figured I would pass along what I have so far.

See attached file that implements this measure:

Complex Selector = 
    VAR __ID = MAX('Table'[invoiceid])
    VAR __specialCodes = { 23, 36 }
    VAR __otherCodes = EXCEPT(VALUES('Table'[Service code]),__specialCodes)
    VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table'),[Service code] IN __specialCodes),"__ID1",[invoiceid])
    VAR __table2 = SELECTCOLUMNS(FILTER(ALL('Table'),[Service code] IN __otherCodes),"__ID2",[invoiceid])
    VAR __table3 = ADDCOLUMNS(__table1,"__CountOther",COUNTX(FILTER(__table2,[__ID2] = [__ID1]),[__ID2]))
    VAR __table4 = SELECTCOLUMNS(FILTER(__table3,[__CountOther]>0),"__serviceID",[__ID1])
RETURN
    IF(__ID IN __table4,1,BLANK())

[Anonymous]

@Greg_Deckler  thanks a lot for your time & effort. Please use the following sample pbi:

https://1drv.ms/u/s!Ag919_pO_UKrgQLIOrUlBri_AeVY?e=T6FhsZ

The one that you are using doesnt have complete meaningful data set.

[Greg_Deckler · Accepted solution]

OK, @Anonymous I *believe* I have this. Required a minor tweak, using your new PBIX. File is attached. Let me know if this works but I may not be able to get back to it until tomorrow. It's pretty late here and I need sleep.

Complex Selector = 
    VAR __ID = MAX('Table'[invoiceid])
    VAR __specialCodes = { 23, 36 }
    VAR __otherCodes = EXCEPT(DISTINCT(ALLSELECTED('Table'[Service code])),__specialCodes)
    VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table'),[Service code] IN __specialCodes),"__ID1",[invoiceid])
    VAR __table2 = SELECTCOLUMNS(FILTER(ALL('Table'),[Service code] IN __otherCodes),"__ID2",[invoiceid])
    VAR __table3 = ADDCOLUMNS(__table1,"__CountOther",COUNTX(FILTER(__table2,[__ID2] = [__ID1]),[__ID2]))
    VAR __table4 = SELECTCOLUMNS(FILTER(__table3,[__CountOther]>0),"__serviceID",[__ID1])
RETURN
    IF(__ID IN __table4,1,BLANK())

[Anonymous]

@Greg_Deckler  really appreciate you help! Thanks😊 You are a saviour🤗

[Greg_Deckler]

My pleasure @Anonymous! Have a good night!