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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Matrix filter is filtering out needed results

Hello,

I have a Matrix that I have a filter built to show only items (Used NDC) that meet a dynamic condition. For each item, a formula (Accumulation Selection M) returns a 1 if true and BLANK() if false. This appears to be working.

Currently, the formula looks at the latest date (4/18/24) and compares the quantity (Type SUM) to the previous available date (4/15/24). If the previous date quantity is >=0 and the latest date quantity is < 0, then a 1 returns, else BLANK(). **Basically, I'm looking for items that went from positive/zero to negative.**

The formula is working, here is the data when I download it in the Matrix with no filters:

The issue becomes when I filter out the Matrix to show only values of 1 for (Accumulation Selection M), only two of the items are shown:

I can not figure out why the other two items are not shown (338114403 & 54643564901).

Any help is appreciated and Thank you!

1 ACCEPTED SOLUTION
Frequent Visitor

This helped. I had to make a few adjustments but below is what worked.

Accumulation Selection M =
VAR MAXDSEL = MAXX(SUMMARIZE(ALLSELECTED('Accumulations'[Date]),'Accumulations'[Date],"Max Date", 'Accumulations'[Date]),[Max Date])
VAR PREVD = CALCULATE( MAX('Accumulations'[Date]), 'Accumulations'[Date] < MAXDSEL )
VAR PREVDATE = OFFSET( -1, ORDERBY('Accumulations'[Date] ) )
VAR PREVSUM = CALCULATE( MAX('Accumulations'[Type Sum]), 'Accumulations'[Date] = PREVD )
VAR CURSUM = CALCULATE( MAX('Accumulations'[Type Sum]), 'Accumulations'[Date] = MAXDSEL )

RETURN
SWITCH(
TRUE(),
SELECTEDVALUE('Accumulation Measures'[Header#]) = BLANK(),
1,

SELECTEDVALUE('Accumulation Measures'[Header#]) = 3,
IF(
NOT ISBLANK( PREVSUM ),
SWITCH(
TRUE(),
PREVSUM >= 0 && CURSUM <0, 1
)
)
)

Thank you for your help!
3 REPLIES 3
Community Support

Below is my table:

The following DAX might work for you:

``````Accumulation Selection M =
VAR MAXD =  MAX('Accumulations'[Date])
VAR PREVD = CALCULATE( MAX('Accumulations'[Sum]), 'Accumulations'[Date] < MAXD )
VAR EVD = CALCULATE(MIN(Accumulations[Sum]),Accumulations[Date] = MAXD)
RETURN
IF(EVD < 0 && PREVD >= 0 , 1 , BLANK())``````

The final output is shown in the following figure:

IF you want to filter Accumulation Selection M, and you can click this:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

This helped. I had to make a few adjustments but below is what worked.

Accumulation Selection M =
VAR MAXDSEL = MAXX(SUMMARIZE(ALLSELECTED('Accumulations'[Date]),'Accumulations'[Date],"Max Date", 'Accumulations'[Date]),[Max Date])
VAR PREVD = CALCULATE( MAX('Accumulations'[Date]), 'Accumulations'[Date] < MAXDSEL )
VAR PREVDATE = OFFSET( -1, ORDERBY('Accumulations'[Date] ) )
VAR PREVSUM = CALCULATE( MAX('Accumulations'[Type Sum]), 'Accumulations'[Date] = PREVD )
VAR CURSUM = CALCULATE( MAX('Accumulations'[Type Sum]), 'Accumulations'[Date] = MAXDSEL )

RETURN
SWITCH(
TRUE(),
SELECTEDVALUE('Accumulation Measures'[Header#]) = BLANK(),
1,

SELECTEDVALUE('Accumulation Measures'[Header#]) = 3,
IF(
NOT ISBLANK( PREVSUM ),
SWITCH(
TRUE(),
PREVSUM >= 0 && CURSUM <0, 1
)
)
)

Thank you for your help!
Frequent Visitor

An update,
It appears that the filter is going off the sum of the filter for all the dates.

When I add the other two items, the Acxcumulation Selection M in the Total equals -1.
How can I adjust the filter to only look at the latest date?

Accumulation Selection M =
VAR MAXD = CALCULATE( MAX ('Accumulations'[Date]) )
VAR PREVD = CALCULATE( MAX('Accumulations'[Date]), 'Accumulations'[Date] < MAXD )
VAR PREVSUM = OFFSET( -1, ORDERBY('Accumulations'[Date] ) )
RETURN

SWITCH(
TRUE(),
SELECTEDVALUE('Accumulation Measures'[Header#]) = "Negative to Positive",
IF (
CALCULATE( [SUM Type SUM (Acc)], PREVSUM ) >= 0 && [SUM Type SUM (Acc)] < 0 ,
1, -1),

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors