Filtering a measure by two attributes

[Anonymous]

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.

[Anonymous · Accepted solution]

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)

[deldersveld]

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]

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.

[v-shex-msft]

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