cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Filtering a measure by two attributes

I have a line chart that I want to display sales for the last two weeks for only one brand, the x axis being order date and the y axis being sales. First I tried:

 

calculate(sales, brand[brand]="brand1",'order date'[order date]>=today()-14)

 

It just gives me the sum of sales for the past 14 days as constant line.

 

Then I tried:

 

sumx(filter(brand,brand[brand]="brand1") && filter('order date'[order date]>=today()-14, sales)

 

I get an error that says: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

Is there any way I can filter my line chart for both of these values?

 

Just fyi, sales is a measure calculated in the Azure Analysis Services layer.

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Filtering a measure by two attributes

I was actually able to find a solution. What I did was a first created a measure filtering by the time period:

 

last two weeks sales = sumx(filter('promise date'[promise date]>=today()-14 && 'promise date'promise date]<=today()), sales)

 

And then I created measures filtering this measure by the brand attribute:

 

brand1 last two weeks sales = sumx(filter(brand,brand[brand]="brand1"), last two weeks sales)

4 REPLIES 4
deldersveld
Advisor

Re: Filtering a measure by two attributes

Instead of hardcoding your filter conditions into a new measure, add Brand and Order Date to your Visual Level Filters and then apply the same filter criteria there.

Anonymous
Not applicable

each Re: Filtering a measure by two attributes

Ok, my mistake, I left out important info in an attempt to keep it simple. I should have included this.

 

My measure is actually orders delivered and there is another measure which is estimated delivery, so I want the chart to include orders delivered in the last two weeks as well as estimated delivery in the next two weeks. Unfortunately the visual level filter can only filter for one direction. I also want the orders delivered and estimated delivered to be two separate lines, so there would need to be two separate measures. Because of this, I would also need to hardcode the brand filters(I also want different lines for different brands), because I can't use the legend with two measures.

 

So as far as I know, I would need to hardcode the filter conditions in order to get that time filter as well as the brand filters, so I am wondering if there is a formula that will allow me to hardcode these two conditions.

 

Sorry for the confusion.

Community Support Team
Community Support Team

Re: each Re: Filtering a measure by two attributes

Hi @Anonymous,

 

If your report contains defined relationships between these tables, you can try to use below formula:

Measure =
CALCULATE (
    Sales[Sales],
    FILTER (
        ALLSELECTED ( 'order date' ),
        'order date'[order date]
            >= TODAY () - 14
    ),
    FILTER ( ALLSELECTED ( brand ), brand[brand] = "brand1" )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Anonymous
Not applicable

Re: Filtering a measure by two attributes

I was actually able to find a solution. What I did was a first created a measure filtering by the time period:

 

last two weeks sales = sumx(filter('promise date'[promise date]>=today()-14 && 'promise date'promise date]<=today()), sales)

 

And then I created measures filtering this measure by the brand attribute:

 

brand1 last two weeks sales = sumx(filter(brand,brand[brand]="brand1"), last two weeks sales)