Multiple filters on the same two columns

[Laszlo]

Hi all,

I am stuck a simple problem. 

Here is my example:

I have a table with event.title and registrant.email.

I added a columns based on the event.title column session names it tags them program1 or 2

Based on the email address I add a column which parse out the domain name. The individual user id will be removed eventually for privacy reasons. 

A user may attend a session multiple time and they can also attend sessions from either programs. 

event.title	registrant.email	Program	domain
session1	email1@domain1.com	program1	domain1
session1	email2@domain1.com	program1	domain1
session1	email1@domain2.com	program1	domain2
session1	email1@domain5.com	program1	domain5
session2	email1@domain1.com	program1	domain1
session2	email2@domain1.com	program1	domain1
session2	email1@domain2.com	program1	domain2
session2	email1@domain3.com	program1	domain3
session2	email1@domain1.com	program1	domain1
session2	email1@domain5.com	program1	domain5
session3	email1@domain1.com	program2	domain1
session3	email2@domain1.com	program2	domain1
session3	email1@domain2.com	program2	domain2
session3	email1@domain3.com	program2	domain3
session3	email1@domain1.com	program2	domain1
session3	email1@domain4.com	program2	domain4
session4	email1@domain1.com	program2	domain1
session4	email2@domain1.com	program2	domain1
session4	email1@domain2.com	program2	domain2
session4	email1@domain3.com	program2	domain3
session4	email1@domain1.com	program2	domain1
session4	email1@domain4.com	program2	domain4

What I am trying to achieve. 

Get a list of domains which attended program1 or 2 only

Get a list of domains which have attended sessions both from program1 and 2

I don't need counts of the domains which participated in both programs, but I need a list of domains rather. 

From the example above domain5 only attended program1 sessions while domain1-4 have have attended sessions from both progams. 

What would be a good approach to solve this problem? 

Thanks!

[az38 · Accepted solution]

Hi @Laszlo 

I see the next solution.

First add new calculated table

Table 2 = DISTINCT(SELECTCOLUMNS('Table';"Domain";'Table'[domain];"Program";'Table'[program]))

Next add measure to Table 2 like this

Measure = calculate(DISTINCTCOUNTNOBLANK('Table 2'[Program]);ALLEXCEPT('Table 2';'Table 2'[Domain]))

Then you can create visuals for example

So, if Measure = 2, this domain is including both program, if measure=1 - the only.

P.S. in your dataset example i see not only domain5, but domain4 also has attented session from the only program2

[az38 · Accepted solution]

Hi @Laszlo 

I see the next solution.

First add new calculated table

Table 2 = DISTINCT(SELECTCOLUMNS('Table';"Domain";'Table'[domain];"Program";'Table'[program]))

Next add measure to Table 2 like this

Measure = calculate(DISTINCTCOUNTNOBLANK('Table 2'[Program]);ALLEXCEPT('Table 2';'Table 2'[Domain]))

Then you can create visuals for example

So, if Measure = 2, this domain is including both program, if measure=1 - the only.

P.S. in your dataset example i see not only domain5, but domain4 also has attented session from the only program2

[ybd]

Is there a JavaScript solution / override for that? 😀

I am using the PowerBI-JavaScript SDK,

the custom visual hierarchy slicer works just fine.

But when i  have try to use the Tuple filter in the JS SDK for (multi columns selection)  it turns out it is not really supported.

This is the Documentaion url

 The power bi team dosn't plan to issue a fix for that - the only why for them to provide a fix is :...

 

Here is an example of a tuple filter for applaying multiple filters on the same two columns  

 slicer_Tuple_Filter = {

    $schema: "http://powerbi.com/product/schema#tuple",
    target:  [
  // same table different  columns selection  
{table: "Table A", column: "column A"}, 
{table: "Table A", column: "column B"} // same table different  columns selection

],

    filterType: 6, // can be int 6 or string 'Tuple'
    operator: "In",
  // value for selected columns  
    values: [

[ {value: "111"}, // value for column A
{value: "222"}// value for column B

]
]
};

Thank you! 🙂

here is the lick again:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40484047-fixing-power-bi-embedded...

[Laszlo]

Thanks very much AZ38! 

I, implmented and it and got it working, but just so I understand what is happening. 

The new table contains the distinct domains in my source table as well as the distinct programs. 

The measure I am not 100% sure how it works. It calculates the distinct without blanks entry of the the program column, so far I am good. The ALLEXCEPT fuction ignores all the filters on the domains column and only apply the filter as it relates to programs. 

Thnaks, 

Laszlo--

[az38 · Accepted solution]

hi @Laszlo 

exactly!

but if to be more correct - new table contains distinct pair domain-program: we are clearing origin table from duplicate pairs. If in your datasource such duplicates are impossible - distincttable is not neccessary step