Data Model - How to show all data rows that do not match one filter?

[Anonymous]

Hello Community

I have currently trouble achieving the following:

I have a set of data which are dictates (recorded). Those dictates have a transcriber, author, duration, department and more attributes. Now I would like to be able to select the department and show all transcriber which have transcribed a dictate for that department in a Pivot table. In another Pivot table i'd like to Show all others (unselected departments) and sum up the total duration of all dictates.

  In this screenshot i've only selected one transcriber but no departments. The total duration is 10037 minutes.

 

Then i select 3 departments. The total amount is still correct (7321+2761 = 10037). The selected departments appear in the left Pivot table, the unselected departments in the right:

   

This is how it should work. I did this with the P() and E() function in set Analysis:

=sum({<Dictates2.transcriber = p(Dictates.transcriber)>*<Dictates2.DiktatAbteilung = e(Dictates.DiktatAbteilung)>}Dictates2.durationSec /60)

But now i'd like to add other filters such as Date, Author or Dictatenumber. I assume i'll have to get all the filter dimensions in separate tables and link them somehow? But I really don't know how to approach this... My current layout in which the above is working:

Note that dictates and dictates2 are the "same" tables.

Can someone help me?

Many Thanks & best reagrds

[juleshartley]

Do you want to apply the filters only to the left hand side and then see the right hand side update for those not included?

If so I'm not sure why you need the 2 tables of data. Why can't you use the complete set in the set analysis expression (using 1 at the beginning of the set expression) and then filter based on the possible values left from your selection?

[juleshartley]

See here

Excluding values in Set Analysis

[Anonymous]

Hi Julian

This works, thanks a lot. But my assumption is right, that with following Expression:

SET fExcludedDictates = 'sum({1<Dictates.transcriber = p(Dictates.transcriber)>*<Dictates.DiktatAbteilung = e(Dictates.DiktatAbteilung)>}Dictates.durationSec /60)';

I'Il have to include all the filters (which are data fields in the same table ([Dictates], like Dictates.transcriber), which shouldn't be ignored due to the 1 in front of the set snalysis?

Filters from other tables (calendar) are working, since this is another set of data, right?

Because in that case I'll just create a table for each "filter" Dimension and link them to the main table.

Best Regards