Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

filters for measures

hi

 

I have created a cumulative sum meassure that calculates order volume cumulative sum.

 

my data contains, <OrderCreationDate>, <order_type>, <licences>

 

and my meassure works on the <licences> column. I plot the measure over time with the line chart and I have certain report-level and page level filters on the <order_type> which excludes certain order types.

 

however, the measure ignores these filters and calculates and plots the sum of all orders regardless of filters. 

 

Cumulative sales = CALCULATE(sum(O365LicenseReport[Licenses]),FILTER(all(O365LicenseReport), O365LicenseReport[OrderCreationDate]<=max(O365LicenseReport[OrderCreationDate])))

 

I even tried to impose visual revel filters and the funny thing is that the order types I want to exclude do not appear in the options because they have been filtered out by the report and page-level filters ????

 

what am I missing here?

2 ACCEPTED SOLUTIONS
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?

 

You could try to modify your formula like below, that should be your expected.

 

Cumulative sales =
CALCULATE (
    SUM ( O365LicenseReport[licences] ),
    FILTER (
        ALLSELECTED ( 'O365LicenseReport' ),
        O365LicenseReport[OrderCreationDate]
            <= MAX ( O365LicenseReport[OrderCreationDate] )
    )
)

Here is my test output.

 

test.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
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

Anonymous
Not applicable


@v-piga-msft wrote:

Hi @Anonymous,

 

If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?

 

You could try to modify your formula like below, that should be your expected.

 

Cumulative sales =
CALCULATE (
    SUM ( O365LicenseReport[licences] ),
    FILTER (
        ALLSELECTED ( 'O365LicenseReport' ),
        O365LicenseReport[OrderCreationDate]
            <= MAX ( O365LicenseReport[OrderCreationDate] )
    )
)

Here is my test output.

 

test.PNG

 

Hope this can help you!

 

Best Regards,

Cherry


YES!!!!!

 

THANK YOU !!!!!!

 

yes !!! now it works...  I understand that I should have used the "ALLSELECTED" rather the "ALL" statement right?

 

if I understand it correctly, ALLSELECTED (as implied) respects filters where as ALL ignores them, correct?

 

View solution in original post

12 REPLIES 12
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?

 

You could try to modify your formula like below, that should be your expected.

 

Cumulative sales =
CALCULATE (
    SUM ( O365LicenseReport[licences] ),
    FILTER (
        ALLSELECTED ( 'O365LicenseReport' ),
        O365LicenseReport[OrderCreationDate]
            <= MAX ( O365LicenseReport[OrderCreationDate] )
    )
)

Here is my test output.

 

test.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable


@v-piga-msft wrote:

Hi @Anonymous,

 

If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?

 

You could try to modify your formula like below, that should be your expected.

 

Cumulative sales =
CALCULATE (
    SUM ( O365LicenseReport[licences] ),
    FILTER (
        ALLSELECTED ( 'O365LicenseReport' ),
        O365LicenseReport[OrderCreationDate]
            <= MAX ( O365LicenseReport[OrderCreationDate] )
    )
)

Here is my test output.

 

test.PNG

 

Hope this can help you!

 

Best Regards,

Cherry


YES!!!!!

 

THANK YOU !!!!!!

 

yes !!! now it works...  I understand that I should have used the "ALLSELECTED" rather the "ALL" statement right?

 

if I understand it correctly, ALLSELECTED (as implied) respects filters where as ALL ignores them, correct?

 

prakritnepal
Helper I
Helper I

You have Filter(all.... in your measure. I believe that is going to return all rows irrespective of visual level filters.  

Anonymous
Not applicable

Can you post a pbix file with expected output?

Anonymous
Not applicable

unfortunatelly I have actual sales data, can't post the pbix. I will try to recreate with dummy data

Anonymous
Not applicable

I see

 

so how do it tell it "respect filters or slicers in the page" ?

 

i don't want to hardwire the filters in the measure code cause that is not make the measure interactive ( I guess I'm stating the obvious)..

 

 

 

I dont think you need to use the filter command at all. The syntax for calculate is calculate(aggregate, filter1, filter 2...). That may work

Anonymous
Not applicable

I just tried that but unfortunatelly DAX complains about the MAX function. I tried

 

Cumulative sales = CALCULATE(

 sum(O365LicenseReport[Licenses]),

 O365LicenseReport[OrderCreationDate]<=max(O365LicenseReport[OrderCreationDate])

)

 

and got back

 

A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Chronis69, 

 

You Max function and the less than equal to column are referring to the same column. I would create a new column with the max value of order creation date like 

 

max val = max(O365LicenseReport[OrderCreationDate]).[Date])

Then  

 

Cumulative sales = CALCULATE(

 sum(O365LicenseReport[Licenses]),

O365LicenseReport[OrderCreationDate]<=YOURTABLENAME[maxval])

 

 

 

 
Anonymous
Not applicable


@prakritnepal wrote:

Chronis69, 

 

You Max function and the less than equal to column are referring to the same column. I would create a new column with the max value of order creation date like 

 

max val = max(O365LicenseReport[OrderCreationDate]).[Date])

Then  

 

Cumulative sales = CALCULATE(

 sum(O365LicenseReport[Licenses]),

O365LicenseReport[OrderCreationDate]<=YOURTABLENAME[maxval])

 

 

 

 

@prakritnepal as per your suggestion I created two columns

1. 

__test max val = max(O365LicenseReport[OrderCreationDate].[Date])

2.

__test run rate =
CALCULATE(SUM(O365LicenseReport[Licenses]),O365LicenseReport[OrderCreationDate]<=O365LicenseReport[__test max val])

 

but then DAX complains that

 

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

 

Why does it have to be so freaging hard to create even the simplest things with DAX??? (btw, I don't understand why it complains, it looks correct to me)

 

 

 Hi @Anonymous,

 

Have you tried using the quick measure feature to create a cumulative sum?






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable


@danextian wrote:

 Hi @Anonymous,

 

Have you tried using the quick measure feature to create a cumulative sum?


I just did. this creates and even more unwated effect. 

 

the code that the auto measure generates is the following

 

Licenses running total in OrderCreationDate =
CALCULATE(
SUM('O365LicenseReport'[Licenses]),
FILTER(
ALLSELECTED('O365LicenseReport'[OrderCreationDate]),
ISONORAFTER('O365LicenseReport'[OrderCreationDate], MAX('O365LicenseReport'[OrderCreationDate]), DESC)
)
)

 

what it does is that it resets the running total accoring to the resolution of the visual. for example if the line charts has years as the X axis it presents the running total each year indepentently. i.e. if the sales are 1000@ y1, 2000@y2 and 3000@y3 instead of a line with points at 

 

1000, 3000 (=1000y1+2000y2), 6000 (=3000y2+3000y3) it gives a line of 

 

1000, 2000, 3000... disaster 😉 ... useful but I don't need a complicated DAX measure for this I simply do a plot and ask to do the sum... see the black line in the pic below

 

pbi.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.