Forum Discussion

Laszlo's avatar
Laszlo
Microsoft Employee
6 years ago
Solved

Multiple filters on the same two columns

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 ...
Tips and Tricks
  • az38's avatar
    az38
    6 years ago

    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's avatar
    az38
    6 years ago

    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

az38's avatar
az38
Community Champion
6 years ago

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

Laszlo's avatar
Laszlo
Microsoft Employee
6 years ago

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's avatar
    az38
    Community Champion
    6 years ago

    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