Forum Discussion

jhollingworth's avatar
jhollingworth
Frequent Visitor
2 years ago
Solved

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 ...
Need Help
  • jhollingworth's avatar
    jhollingworth
    2 years ago

    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!
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi jhollingworth ,

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.

 

jhollingworth's avatar
jhollingworth
Frequent Visitor
2 years ago

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!