cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MoOv Resolver II
Resolver II

Issues with filter propagation and ISINSCOPE

Hello everybody,

 

EDIT: I found a different way to build the report and don't need help with this anymore. Unfortunately this doesn't solve the problem I described in general, so I can't post a solution for that. // I'm trying to build a measure using ISINSCOPE. Not very complex in itself, but it does not work as I expect it to. Details below.

 

Simplified situation:

  1. There are two tables of interest, factValues and dimArticle, with an active many-to-one relationship from factValues[ArticleID] to dimArticle[ArticleID]. As expected, I can filter factValues by any column from dimArticle, both via report/page/visual level filters and slicers. I want the report consumers to be able to filter by dimArticle[Article] as they don't know all the ArticleIDs.
  2. I want to create a matrix visual with factValues[ValueID] as row value and measure "Test = ISINSCOPE ( factValues[ValueID] )" as value. [Actually, I nest this into an IF statement, but the simplified measure suffices to illustrate the problem.] I use a slicer with dimArticle[Article] to display only ValueIDs related to specific Articles. Filtering the rows this way works as long as the measure is not yet selected and only factValues[ValueID] is displayed in the visual (shown below).
  3. However, as soon as I add the measure to the visual, more rows are being shown - all values from factValues[ValueID] to be precise, and TRUE for the Measure in every row (also shown below). I can't filter the rows by dimArticle[ArticleID] anymore, neither via page level filter nor via slicer. What I still can do is: I can use factValues[ArticleID] in a slicer to filter as expected, but as mentioned above I want to filter by dimArticle[Article]. That's what relationships are there for after all.

Does anybody know why [ValueID] is not filtered anymore after adding the measure? I feel like (and hope) I'm missing something very basic right now. Help is much appreciated!

 

Modell.PNGSimplified modelOhneMeasure.PNGVisual without measureMitMeasure.PNGVisual with measure

3 REPLIES 3
MoOv Resolver II
Resolver II

Re: Issues with filter propagation and ISINSCOPE

As an afterthought, neither can I use dimArticle[Article] in the row hierarchy to filter the results after the measure was added to the visual. If I use it there, all values from the ValueID column are being shown for every article, as if there was no existing relationship between the tables at all.

Community Support
Community Support

Re: Issues with filter propagation and ISINSCOPE

Hi @MoOv ,

 

I tested it, and got the same issue. But I find that you can filter with the columns in the same table which the visual data is in.

 

Best Regards,

Icey

 

MoOv Resolver II
Resolver II

Re: Issues with filter propagation and ISINSCOPE

Hello @Icey ,

 

that's right, you can filter by the columns in the fact table. But you can't always do that in reports - maybe there are categories in your dimension table you want to use as a filter, or - as in my case - the report consumers would have to deal with unintelligible IDs instead of article names in slicers etc. Therefore, filtering by a column from the fact table is often not helpful. Still, thanks for replying and indicating a possible workaround.

 

Regards

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors