cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpollozhani
Regular Visitor

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:

dpollozhani_0-1634049838845.png

 

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

dpollozhani_1-1634049976759.png

 

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:

dpollozhani_2-1634050056301.png

 

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:

dpollozhani_3-1634050250230.png

 

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

dpollozhani_4-1634051321039.png

 

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!

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community 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.

View solution in original post

2 REPLIES 2
Icey
Community Support
Community 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.

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.