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

Reply
jhollingworth
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:

jhollingworth_0-1713794134373.png

 

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:

 

jhollingworth_1-1713794184670.png

 

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

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!

View solution in original post

3 REPLIES 3
v-xiandat-msft
Community Support
Community Support

Hi @jhollingworth ,

Below is my table:

vxiandatmsft_0-1713852540713.png

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:

vxiandatmsft_1-1713852594805.png

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

vxiandatmsft_2-1713852659352.png

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.

 

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!
jhollingworth
Frequent Visitor

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

jhollingworth_1-1713805008619.png

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 Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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