From if statement in table to measure

[timpoismans]

Hey all,

I recently encountered a problem involving a lot of filtering through set analysis.

To make things easier, I simplified the filtering based on priority level.

A little clarification:

A client has 10 "super articles". There's a seperate view for each article.

Now, the client sells items. And items are built up out of articles, including super articles.

The problem was: under which super article do we show an item when it's built up out of more than one super article.

I made 4 priority levels. In the application, the field is called "Filter weight".

Now based on those levels, I can decide which item is shown under which super article.

The super articles with priority(Filter weight) = 1 don't require extra filtering as they need to be shown under their respective super article.

When an item is built up out of super articles with priority 2, 3 or 4, I do require extra filtering.

The extra filtering is done as such:

     If(not WildMatch(Aggr(Concat([Filter weight]),Item),'*1*')>0,

          If(WildMatch(Aggr(Concat([Filter weight]),Item),'*2*')>0,Item)

     )

This is for a super article which belongs to priority level 2.

     If(not WildMatch(Aggr(Concat([Filter weight]),Item),'*1*')>0 AND not WildMatch(Aggr(Concat([Filter weight]),Item),'*2*')>0,

          If(WildMatch(Aggr(Concat([Filter weight]),Item),'*3*')>0,Item)

     )

This is for a super article which belongs to priority level 3.

The filter is used on the dimension Item.

In my tables there's no problem using this type of filtering for the items I don't want to see.

However, I don't seem to be able to incorporate this in a measure NOT in a table, which is due to there not being a structure anymore. I tried to solve this with the Aggr function, but it seems I can't figure it out.

An example:

For Order 220150, we've got Item 40. Item 40 is built up out a number of articles, of which 3 super articles which belong to different priority levels.

Through the filter for a super article which belongs to priority level 2, I can determine it is shown under the correct super article.

The filter is done based on a Concat of the filter weight of all the articles(products) under the item:

What I want to achieve is that I can make the Sum(# Net Amount) based on this filterting.

Which works fine in the table above, as we got a structure of Document Number ==> Item ==> Product.

But when I want to make a measure for the Sum(# Net Amount) for a super article belonging to priority level 2 in a KPI, I can't seem to get it to work through an Aggr function.

Is it possible to edit the filtering so it's usable in a measure outside of a table?

P.S.: As attachement, I've put an Excel with the data shown in the screenshots.

[sunny_talwar]

Not entirely sure what you have, but see if this works

Sum(Aggr(If([Filter Weight] = Min(TOTAL <[Document Number], Item> [Filter Weight]), Sum([# Net Amount])), [Document Number], Item, [Product Group]))

[timpoismans]

Hi Sunny

Sorry for the late response.

In general the measure seems to work.

If I'd use an extra set analysis with this measure, where exactly would I fit it in?

Or is it even possible to do that?

[sunny_talwar · Accepted solution]

It is possible.... replace {<SetAnalysis>} in the following places

Sum({<SetAnalysis>}Aggr(If(Only({<SetAnalysis>}[Filter Weight]) = Min({<SetAnalysis>}TOTAL <[Document Number], Item> [Filter Weight]), Sum({<SetAnalysis>}[# Net Amount])), [Document Number], Item, [Product Group]))

[timpoismans]

Ah, I forgot the Only(). Should've thought of that.

The measure seems to work perfectly.

Thanks for the quick replies, Sunny!