Forum Discussion

dpollozhani's avatar
dpollozhani
Icon for Helper I rankHelper I
4 years ago
Solved

Include only articles that have moved consecutively during selected months

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!

 

  • 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.

2 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support

    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.

    • dpollozhani's avatar
      dpollozhani
      Icon for Helper I rankHelper I

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