cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chronis69 Member
Member

Re: filters for measures


@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)

 

 

Community Support Team
Community Support Team

Re: filters for measures

Hi @chronis69,

 

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

Highlighted
chronis69 Member
Member

Re: filters for measures


@v-piga-msft wrote:

Hi @chronis69,

 

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 154 members 2,067 guests
Please welcome our newest community members: