topic Re: Dynamic filter based in multiple columns and criteria in DAX Commands and Tips

Dynamic filter based in multiple columns and criteria

Giovani — Thu, 29 Oct 2020 01:56:20 GMT

I am struggling with something that may be simple to solve. By year and month, I want to make a dynamic bar chart to present counts by a distinct ID that fits criteria based on columns Filter1 and Filter2. For simplicity, I am only using as an example a few lines from a large table, assuming all are from the same year and month.

ID	Filter1	Filter2
ID1	A	D
ID1	B	D
ID2	A	F
ID2	B	E
ID3	A	D
ID3	B	F
ID4	B	D
ID4	C	D
ID5	B	D
ID5	C	E
ID6	A	D
ID6	B	D
ID6	C	D

An example of questions I am trying to answer and visualize are

1-How many times A and B were concomitantly present in an ID? Answer = 4 (ID1;ID2;ID3;ID6). This would later be used to answer a similar question applied to B and C, or for the three of them A, B, and C.

2- How many times A and B were concomitantly present in an ID, and A had Filter2= D and B had Filter2= D?

Answer= 2 (ID1; ID6)

2- How many times A and B were concomitantly present in an ID, and A had Filter2= D and B had Filter2= E?

Answer= 0

Any help in how I could do this is welcome!

Re: Dynamic filter based in multiple columns and criteria

wdx223_Daniel — Thu, 29 Oct 2020 02:53:21 GMT

Giovani might these code help

ID_Count:=COUNTROWS ( FILTER ( VALUES ( SampleData[ID] ), VAR _Filter1 = CALCULATETABLE ( VALUES ( SampleData[Filter1] ), ALLEXCEPT ( SampleData, SampleData[ID] ) ) RETURN AND ( CONTAINSROW ( _Filter1, "A" ), CONTAINSROW ( _Filter1, "B" ) ) ) ) ---------------------------- ID_Count2:=COUNTROWS ( FILTER ( VALUES ( SampleData[ID] ), VAR _Filter1 = CALCULATETABLE ( SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ), ALLEXCEPT ( SampleData, SampleData[ID] ) ) RETURN AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "D" ) ) ) ) ---------------------- ID_Count3:=COUNTROWS ( FILTER ( VALUES ( SampleData[ID] ), VAR _Filter1 = CALCULATETABLE ( SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ), ALLEXCEPT ( SampleData, SampleData[ID] ) ) RETURN AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "E" ) ) ) )

Re: Dynamic filter based in multiple columns and criteria

Giovani — Thu, 29 Oct 2020 12:26:38 GMT

Many thanks to wdx223_Daniel this is perfect and is exactlly what I was looking for!