topic Re: Help setting up a Slicer / Filter with AND logic selecting from one field and filtering another. in DAX Commands and Tips

Help setting up a Slicer / Filter with AND logic selecting from one field and filtering another.

FDYBI — Mon, 25 Apr 2022 12:56:56 GMT

I may not be explaining it very well, I'm finding it hard to actually describe what I'm trying to do. I have a set of 'Types' and 'Sub Types' associated with various reports. The each report may have multiple types and subtypes. Originally each report ID would be listed and have the type and subtype for that report but if it had multiple types or subtypes it would be duplicated ie: report 21213 would have type "XX" and subtype "XY" and then the next row may have that same report 21213 with the type "YY" subtype "YX" or. I now have it grouped together on report ID and concatenated a new column for type and subtypes so it now has report 21213 types "XX, YY" subtypes "XY,YX".

The only problem is I need to be able to filter with the AND logic, and I know I can do it prety easily just by showing the filtes pane, going to advanced, and putting the contains AND contains logic but I want it to be a bit easier for the end users and not have to do that / type anything in. So I'm wanting a slicer or check box list that I can have it pull possible types in, which I have still as simply "Type" and look in the "Types" for if it contains that, and on multiple selections contains that AND another selection. ie: show only reports containing Type XX AND YY.

I've tried the following measure to no avail:

Search Measure =

VAR mycount =

COUNTROWS (

FILTER (

VALUES ( 'Incident Data'[Type] ),

SEARCH ( [Type], SELECTEDVALUE ( 'Combined Incident Types'[Incident Type] ),, BLANK () )

)

RETURN IF ( mycount > 0, "Found" , "Not Found")

Re: Help setting up a Slicer / Filter with AND logic selecting from one field and filtering another.

amitchandak — Mon, 25 Apr 2022 17:05:58 GMT

FDYBI , refer if this video can help

And for Selected Values, All selected values are present: https://youtu.be/X5T4rIZovHk

Re: Help setting up a Slicer / Filter with AND logic selecting from one field and filtering another.

Anonymous — Thu, 28 Apr 2022 07:12:04 GMT

Hi FDYBI ,

Please refer to my pbix file to see if it helps you.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI0MjRW0lGKjAQREUqxOgixiAgQEQkXM8EiZgoTQ9JrCjMPpC4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [report = _t, #"type" = _t, #"sub-type" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"report", Int64.Type}, {"type", type text}, {"sub-type", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"report"}, {{"Count", each Text.Combine([type], ","),type text}, {"subtype", each Text.Combine([#"sub-type"],","),type text}}) in #"Grouped Rows"

If I have misundstood your meaning, please provide your desired output and pbix file without privacy information.

How to Get Your Question Answered Quickly

Best Regards

Community Support Team _ Polly

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

Re: Help setting up a Slicer / Filter with AND logic selecting from one field and filtering another.

Anonymous — Fri, 06 May 2022 06:35:08 GMT

Hi FDYBI ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Best Regards

Community Support Team _ Polly

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

Re: Help setting up a Slicer / Filter with AND logic selecting from one field and filtering another.

FDYBI — Mon, 09 May 2022 17:32:31 GMT

It made sense for grouping; however, I did that myself through the initial bringing in of the data in power query. I have no issue with concatenating the data into new columns. What I'm having an issue with is creating a slicer, or slicers, to perform with an AND logic so someone can click on multiple types and show if and it contains the item selected AND another item selected. Ie: a slicer / selectable filter that you can click "XX" AND "YY" and it will show you 21213 and 21215 in your example but not 21214.