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!
jhollingworth's avatar
jhollingworth
Frequent Visitor
2 years ago

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),