Include only articles that have moved consecutively during selected months

dpollozhani — Tue, 12 Oct 2021 15:17:46 GMT

Hi everyone,

This is my first post here, as I have only recently started working with PBI (I'm coming from a Qlik world, but that might change). With that in mind, I hope that I'm able to formulate my needs as clear as possible.

So, I have a problem that I've been stuck for quite some while now.

Intro

I want to analyze the average sales price development on a monthly basis. The requirements are 1) Calculate the average price per company, article, and year-month; then sum these values up (it's specific to our situation); 2) Only analyze articles that have moved consecutively during the time period that is selected by the user, i.e. if I select year 2021 and months jan-sept, I need to dynamically include only articles that have moved at least once per month during 2021 jan-sept.

Month measures

For this I first deciced to calculate how many months are available in total (with a minor tweak to remove the current month under certain conditions), a number that should depend only on the time selection:

Then, I calculate the months that are available, accepting all filters:

Lastly, I create a new measure that is a straight comparison between above two measure, i.e. a boolean that I mean to use as a filter later:

So far so good, in as much as these measures verifiably work.

Article filter

Now comes the difficult part: creating a filter of articles where this latter condition is true. I have tried many things that don't work, so this is what I have at the moment:

This works - however, it is extremely slow. As you may have already realized, the FILTER(...) with my boolean filter condition is the culprit. The measure takes about 15-20 seconds to calculate upon each new selection. The datamodel is neither complex nor large (I'm working with a tiny subset of the real data).

Note that the TREATAS comes from the fact that I have a relationship (many:many, if that matters) between my Fact table and a another table ("ASP") where all prices are already calculated per article, company and year-month (it's not connected to the calendar, hence TREATAS). The thinking behing this was simply to avoid summarizing too much on the fly.

Data model

My questions are: 1) Has anyone ever done this specific kind of analysis; 2) Is there anything you see directly here that should change for a better performance?

I will also gladly accept any improvement suggestions.

Thanks in advance!

Re: Include only articles that have moved consecutively during selected months

Icey — Fri, 15 Oct 2021 05:58:27 GMT

Hi dpollozhani ,

Try to use the measure below as a filter:

Count = COUNTROWS ( EXCEPT ( VALUES ( 'Calendar'[Year Month] ), VALUES ( 'Sales'[Year Month] ) ) ) = 0

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Include only articles that have moved consecutively during selected months

dpollozhani — Fri, 15 Oct 2021 10:54:40 GMT

This does work much, much better, thank you. Do you have an idea of what the reason for the improvement is?

topic Include only articles that have moved consecutively during selected months in DAX Commands and Tips

Include only articles that have moved consecutively during selected months

Intro

Month measures

Article filter

Data model

Re: Include only articles that have moved consecutively during selected months

Re: Include only articles that have moved consecutively during selected months