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 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!

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!

3 Replies

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