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

Ignoring page filters in measure

Hello guys,

 

I got some issues by trying a calculate measure "Stock (org)" quantity to ingnore page filters.

I have three tables: Item and Calendar as dimension tables and Item Ledger Entry as fact table.

Stock (org) measure:

Stock (org) = CALCULATE(
    SUM('Item Ledger Entry'[Quantity]),
    FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date]<=MAX('Calendar'[Date])
 ))

Everything worked fine, till I had a task to create a table where show just purchase operation (Item name/Quantity/ and stock for end date), purchase operations (Item Ledger Entries [Source Type] = 2). I modified formula:

Stock (1) = CALCULATE(
    SUM('Item Ledger Entry'[Quantity]), ALLEXCEPT('Item Ledger Entry', 'Item Ledger Entry'[Item No_]),
    FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date]<=MAX('Calendar'[Date])
 ))

But result is that it shows total stock amount for every Item.

Purchase stock_1.png

Do you have any solutions how to modify formula, that fits my needs.

 

Example:

https://drive.google.com/file/d/18sR4YR79fLkCikuNw6lF_fFkM-VsR-4L/view?usp=sharing

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Ignoring page filters in measure

Hi @rimgri,

 

It seems you just need to remove the filter 'Item Ledger Entry'[Source Type]. Is it this one?

Stock (1) =
CALCULATE (
    SUM ( 'Item Ledger Entry'[Quantity] ),
    ALL ( 'Item Ledger Entry'[Source Type] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Ignoring-page-filters-in-measure

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Ignoring page filters in measure

Hey,

 

I have to admit that I do not fully understand your requirements, maybe you might consider to explain in more detail what you have to achieve.

 

But nevertheless, maybe this measure provides what you are looking for:

Stock (1) = CALCULATE(
sum('Item Ledger Entry'[Quantity])
--,ALLEXCEPT('Item Ledger Entry', 'Item Ledger Entry'[Item No_]),
,ALL('Item Ledger Entry')
,'Item Ledger Entry'[Source Type] = 2
,KEEPFILTERS(VALUES('Item'[Merged]))
,FILTER(
ALLSELECTED('Calendar')
,'Calendar'[Date]<=MAX('Calendar'[Date])
)
)


Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
rimgri Member
Member

Re: Ignoring page filters in measure

Nope, it's sums just rows where Source Type is 2.

Let me be a little bit more clear.

I prepared a table wich has all Purchase info (Page filter set up Source Type = 2) and in this table I need one more column (the red one) wich has to ignore Page filter Source Type =2 (show sum of all transactions for specific date).

 

Purchase 1.png

Can you help me?

 

v-jiascu-msft Super Contributor
Super Contributor

Re: Ignoring page filters in measure

Hi @rimgri,

 

It seems you just need to remove the filter 'Item Ledger Entry'[Source Type]. Is it this one?

Stock (1) =
CALCULATE (
    SUM ( 'Item Ledger Entry'[Quantity] ),
    ALL ( 'Item Ledger Entry'[Source Type] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Ignoring-page-filters-in-measure

 

 

Best Regards,
Dale

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

Re: Ignoring page filters in measure

Thanks, @v-jiascu-msft it's working!

 

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 288 members 3,409 guests
Please welcome our newest community members: