what if scenarios with complex filtering

Question

Hi everyone,&nbsp;&nbsp;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.&nbsp;My fact table is like the below:Author&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PublicationID&nbsp; &nbsp; &nbsp; CitationsBJ&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10GT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10GT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5GT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3SR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10SR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3SR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp;&nbsp;And my dim table is the following:&nbsp;&nbsp;Author&nbsp; &nbsp; SchoolBJ&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; School of Business&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;GT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;School of MedicineSR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;School of Medicine&nbsp;One publication can belong to many authors and schools, authors &amp; 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. )&nbsp;I want to calculate distinct count and other measures of publicationsIDs belonging to NON SELECTED schools.&nbsp;&nbsp;How can I count the publicationIDs where none of the authors belong to the selected school(s)?&nbsp;&nbsp;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:&nbsp;PubIDs of nonselected =CALCULATE(DISTINCTCOUNT(my fact table'[PubID]), EXCEPT(ALL(my dim table[School]),ALLSELECTED(my dim table[School])))&nbsp;Another failed solution: I have tried setting up a calculatedtable first to isolate the publicationIDs of selected school(s):&nbsp;IDsofselected = CALCULATETABLE(VALUES('my fact table'[PubID]))&nbsp;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&amp;CALCULATETABLE, CROSSFILTER, TREATAS and creating a duplicate of the fact table to get the distinct count of&nbsp;publicationIDs&nbsp;belonging to NON SELECTED schools excluding those which also belong to selected schools.&nbsp;&nbsp;Any help is greatly appreciated.&nbsp;

agoo · Accepted Answer

Thanks a lot Amit! I used your measure and modified it to get what I need the following way:&nbsp;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)returnCALCULATE(DISTINCTCOUNT(Rankings_metrics_QSTHE[PubID]), filter(all(Rankings_metrics_QSTHE) , Rankings_metrics_QSTHE[PubID] in _IDs_allother))

Forum Discussion

what if scenarios with complex filtering

2 Replies

Related content

Data Filter Scenario

Dynamic Complex Filtering with slicers

Replicating complex business scenario of sql in Power BI

Complex Filtering

Complex dynamic role filter