topic what if scenarios with complex filtering in DAX Commands and Tips

what if scenarios with complex filtering

AgoO — Mon, 24 Jan 2022 03:48:18 GMT

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.

Re: what if scenarios with complex filtering

amitchandak — Mon, 24 Jan 2022 03:57:01 GMT

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

Re: what if scenarios with complex filtering

AgoO — Mon, 24 Jan 2022 22:33:36 GMT

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))