what if scenarios with complex filtering

[AgoO]

Hi everyone, 

I am setting up a scenario-testing with what if parameters. My modelling is related to school performance in certain fields to which multiple schools can contribute. I would like to be able to calculate measures based on user selection of parameters and schools, while keeping all other values constant.

My fact table is like the below:

Author                                PublicationID      Citations

BJ                                               A                    10

GT                                              A                    10

GT                                              B                      5

GT                                              C                      3

SR                                              A                    10

SR                                              C                      3

SR                                              D                      0 

And my dim table is the following: 

Author    School

BJ            School of Business          

GT           School of Medicine

SR           School of Medicine

One publication can belong to many authors and schools, authors & schools can have many publications and schools can have many authors.

(This is a simplified version of my model because I also have other dim tables and publicationIDs can belong to various fields. )

I want to calculate distinct count and other measures of publicationsIDs belonging to NON SELECTED schools.  

How can I count the publicationIDs where none of the authors belong to the selected school(s)? 

If I am calculating the number of PublicationIDs belonging to non-selected schools with the measure below publications that belong to both selected and non-selected schools are counted in, so this gives me an incorrect result: 

PubIDs of nonselected =

CALCULATE(DISTINCTCOUNT(my fact table'[PubID]), EXCEPT(ALL(my dim table[School]),ALLSELECTED(my dim table[School])))

Another failed solution: I have tried setting up a calculatedtable first to isolate the publicationIDs of selected school(s): 

IDsofselected = CALCULATETABLE(VALUES('my fact table'[PubID])) 

And then I've tried many ways to get the IDS that are all other IDs except the ones in this table. I experimented with EXCEPT&CALCULATETABLE, CROSSFILTER, TREATAS and creating a duplicate of the fact table to get the distinct count of publicationIDs belonging to NON SELECTED schools excluding those which also belong to selected schools. 

Any help is greatly appreciated. 

[amitchandak]

@AgoO , you can try a measure like

Measure =
var _tab = EXCEPT(ALL('my dim table'[School]),ALLSELECTED('my dim table'[School]))
var _tab2 = summarize(filter('my dim table', 'my dim table'[School] in _tab ), [Author])
return
CALCULATE(DISTINCTCOUNT('my fact table'[PubID]), filter(all('my fact table'[Author]) , 'my fact table'[Author] in _tab2))

But these measure are suceessful my dim table is independent table do not join with fact

Refer this video

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

[AgoO · Accepted solution]

Thanks a lot Amit! I used your measure and modified it to get what I need the following way: 

PubIDlists =

var _IDsofselectedschool = CALCULATETABLE(VALUES(Rankings_metrics_QSTHE[PubID]),ALLSELECTED(Rankings_AuthorInternal[School/Department]))

var _IDs_allother = EXCEPT(CALCULATETABLE(VALUES(Rankings_metrics_QSTHE[PubID]), REMOVEFILTERS(Rankings_AuthorInternal[School/Department])), _IDsofselectedschool)

return

CALCULATE(DISTINCTCOUNT(Rankings_metrics_QSTHE[PubID]), filter(all(Rankings_metrics_QSTHE) , Rankings_metrics_QSTHE[PubID] in _IDs_allother))