DAX Filtering and Identifying Multiples Across an Attribute

[Anonymous]

Relativly new to DAX and would appreciate any input.  I have data that looks like the below and I have a sclicer on Event_Type.  I want to filter on multiple Event_Types and then be able to filter to only include those records that have Audited and Invoiced set to Y for multiple rows accross each Event_ID. 

Event_ID	Row_ID	Audited	Invoiced	Event_Type
1	10	Y	Y	Urgent
1	20	Y	Y	Emergency
1	30	N	N	Routine
2	40	N	N	Urgent
2	50	Y	Y	Emergency
2	60	Y	Y	Routine
3	70	Y	Y	Urgent
3	80	Y	Y	Emergency
3	90	Y	Y	Routine

So, if I filter on Event Types of Urgent and Emergency, I would like to be able to have another slicer (i.e. Has Multiple) that will return the records that have Audited and Invoiced set to Yes.

In this example (filtering for Urgent and Emergency) I would get Row_IDs 10, 20, 70, 80.

I have tried to create measures on top of custom columns to return counts and use those as my “Has Multiple” filter but I can’t get the measure to change when I filter by Event Types.  Any advice how to implement the functionality would be appreciated

[Anonymous]

Hi @Anonymous ,

If I understand correctly, the issue is that you want to filter on multiple Event Types. Please try the following methods and check if they can solve your problem:

1.Create the simple Table and Table 2. 

2.Add Create a measure 2. Enter the following DAX formula.

Measure 2 = 
var tmp=SELECTCOLUMNS('Table 2',"ET",[Event_Type])
var _str=CONCATENATEX(tmp,[ET],",")
var _a=SELECTEDVALUE('Table'[Audited])
var _b=SELECTEDVALUE('Table'[Invoiced])
var _c=SELECTEDVALUE('Table'[Event_Type])
return
IF(ISFILTERED('Table 2'[Event_Type]),IF(CONTAINSSTRING(_str,_c)&&_a="Y"&&_b="Y",1),1)

3.Move measure 2 to the filters.

4.Select the slicer visual and move the Table 2 Event_Type to the slicer.

5.Select the Emergency and Urgent, the result is shown below.

Looking forward to your reply.

Best Regards

[adavtian]

Hi.  Thanks for the reply.  The idea is that the user will select two or more Even_Types.  Then they would like to further filter to only include records that occure more than once within an given Event_ID for the selected Event Types. 

I was thing of creating a column (like CountInEvent) that would calculate and update based on the even_type selection and then using that as a filter.  

The key point is that if the user changes the Event_Types selected (via a slicer) the CountInEvent should change.

This would be what it would look like without filters.

If I filtered on Emergency and Routine it would look like the below (Since only these values have Audited and Invoiced set to Y)

Then I would filter on CountInEvent to only get values greater than 1.

[Ritaf1983]

Hi @Anonymous 

Please show what is the desired output.

[adavtian]

The Selecte slicer values for Entry Type would be 

Urgent

Emergency

The I would use a flag (or another method to select where Autided and Invoiced = Y) 

This would give me the following result:

Event_ID	Row_ID	Audited	Invoiced	Event_Type
1	10	Y	Y	Urgent
1	20	Y	Y	Emergency
3	70	Y	Y	Urgent
3	80	Y	Y	Emergency