cancel
Showing results for
Did you mean:
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.

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

Example:

1 ACCEPTED SOLUTION

Accepted Solutions
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] )
)
)
```

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

## 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
Proud to be a Datanaut!
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).

Can you help me?

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] )
)
)
```

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.
Member

## Re: Ignoring page filters in measure

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

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 113 members 1,666 guests
Recent signins: