Forum Discussion

lancersc's avatar
lancersc
Icon for Helper I rankHelper I
2 years ago
Solved

DAX IF statement for 2 filtered columns

Hello!

 

So its been about 1 week and I can't seem to figure out how to do this and I just give up.

I just want to compare 1 column filtered twice and probably beyond that after I make sense of this.

Table Example

 

Item IDCvP
A2024
A2023
B2024
C2024
C2023

 

(Item ID where CvP = 2024) = (Item ID where CvP = 2023)
If true = Matched, Else = Unmatched

 

A is matched 

B is unmatched because it didn't exist last 2023

C is matched

 

Desired Result

Item IDMatching
AMatched
BUnmatched
CMatched

 

Below is what I used on DAX

 

 

 

 

 

RD Filter =
IF(
(CALCULATE(
SUM('RAW DATA (INVOICE)'[Item ID]),'RAW DATA (INVOICE)'[CC vs PP] = "2024"))
=
(CALCULATE(
SUM('RAW DATA (INVOICE)'[Item ID]),'RAW DATA (INVOICE)'[CC vs PP] = "2023"))
, "Matched","Unmatched")

 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi lancersc 

     

    Thanks for the reply from _AAndrade, please allow me to provide another insight:

    Here I create a measure:

    RD Filter =
VAR _currentID =
    SELECTEDVALUE ( 'RAW DATA (INVOICE)'[Item ID] )
RETURN
    IF (
        CALCULATE (
            SELECTEDVALUE ( 'RAW DATA (INVOICE)'[Item ID] ),
            FILTER (
                ALLSELECTED ( 'RAW DATA (INVOICE)' ),
                'RAW DATA (INVOICE)'[Item ID] = _currentID
                    && 'RAW DATA (INVOICE)'[CvP] = 2024
            )
        )
            = CALCULATE (
                SELECTEDVALUE ( 'RAW DATA (INVOICE)'[Item ID] ),
                FILTER (
                    ALLSELECTED ( 'RAW DATA (INVOICE)' ),
                    'RAW DATA (INVOICE)'[Item ID] = _currentID
                        && 'RAW DATA (INVOICE)'[CvP] = 2023
                )
            ),
        "Matched",
        "Unmatched"
    )

    The result is as follow:

     

    Best Regards

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

4 Replies

  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi lancersc,

    Could you provide a sample table with some data that represents your model, so I can take a look?

    • lancersc's avatar
      lancersc
      Icon for Helper I rankHelper I
      2 years ago

      Well, this is the only thing I am allowed to share rn. Not sure if it is any help.
      On the left side of the Item ID should be the Matched/Unmatched.

      • _AAndrade's avatar
        _AAndrade
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        Where is this column  "CC vs PP" and their data? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi lancersc 

     

    Thanks for the reply from _AAndrade, please allow me to provide another insight:

    Here I create a measure:

    RD Filter =
VAR _currentID =
    SELECTEDVALUE ( 'RAW DATA (INVOICE)'[Item ID] )
RETURN
    IF (
        CALCULATE (
            SELECTEDVALUE ( 'RAW DATA (INVOICE)'[Item ID] ),
            FILTER (
                ALLSELECTED ( 'RAW DATA (INVOICE)' ),
                'RAW DATA (INVOICE)'[Item ID] = _currentID
                    && 'RAW DATA (INVOICE)'[CvP] = 2024
            )
        )
            = CALCULATE (
                SELECTEDVALUE ( 'RAW DATA (INVOICE)'[Item ID] ),
                FILTER (
                    ALLSELECTED ( 'RAW DATA (INVOICE)' ),
                    'RAW DATA (INVOICE)'[Item ID] = _currentID
                        && 'RAW DATA (INVOICE)'[CvP] = 2023
                )
            ),
        "Matched",
        "Unmatched"
    )

    The result is as follow:

     

    Best Regards

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