Forum Discussion

edtm's avatar
edtm
Frequent Visitor
2 years ago
Solved

Label Customer as "Resurrect"

Hello.
I am trying label a type customers who fall into the criteria below as "resurrect":

1) In 2023 year and 3rd quarter, only purchased one unit.
2) In 2023 and 4th quarter, purchased at least one unit. 

 

Base on this criteria, Bob should be labeled as "resurrect" but not James.  Is there a Custom Column dax formula that can do this?

 

Add:  At the surface, this seems like a simple ask.  It's easy to isolate for 1) or 2) but I've not been successful at isolating for both.   I think there's some row context issue I don't quite grasp. 

 

Thanks in advance.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
    Please check the below picture and the attached pbix file.

    It is for creating a new column in the Name table.
    I hope this helps to create a solution for your semantic model.

     

     

     

     

     

    Resurrect CC =
VAR _conditionone =
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                SUMMARIZE ( RELATEDTABLE ( Data ), Period[Year-Qtr] ),
                Period[Year-Qtr] = 202303
            ),
            CALCULATE ( SUM ( Data[Units] ) ) = 1
        )
    ) = 1
VAR _conditiontwo =
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                SUMMARIZE ( RELATEDTABLE ( Data ), Period[Year-Qtr] ),
                Period[Year-Qtr] = 202304
            ),
            CALCULATE ( SUM ( Data[Units] ) ) >= 1
        )
    ) = 1
RETURN
    IF ( _conditionone && _conditiontwo, "Yes" )

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
    Please check the below picture and the attached pbix file.

    It is for creating a new column in the Name table.
    I hope this helps to create a solution for your semantic model.

     

     

     

     

     

    Resurrect CC =
VAR _conditionone =
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                SUMMARIZE ( RELATEDTABLE ( Data ), Period[Year-Qtr] ),
                Period[Year-Qtr] = 202303
            ),
            CALCULATE ( SUM ( Data[Units] ) ) = 1
        )
    ) = 1
VAR _conditiontwo =
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                SUMMARIZE ( RELATEDTABLE ( Data ), Period[Year-Qtr] ),
                Period[Year-Qtr] = 202304
            ),
            CALCULATE ( SUM ( Data[Units] ) ) >= 1
        )
    ) = 1
RETURN
    IF ( _conditionone && _conditiontwo, "Yes" )

     

    • edtm's avatar
      edtm
      Frequent Visitor
      2 years ago

      Thanks Jihwan!  I do think this will work.  However, I am having adapting the dax to my file.  Could you help me trouble shoot? 

       

      My model is very simple:



      And, this is how I've adapted your dax so far but the "Yes" hasn't appeared next to Bob:

      Resurrect CC =
      VAR _conditionone =
          COUNTROWS (
              FILTER (
                  CALCULATETABLE (
                      SUMMARIZE ( 'Original Table', 'Original Table'[Year-Qtr] ),
                      'Original Table'[Year-Qtr] = "202303"
                  ),
                  CALCULATE ( SUM ('Original Table'[Units] ) ) = 1
              )
          ) = 1
      VAR _conditiontwo =
          COUNTROWS (
              FILTER (
                  CALCULATETABLE (
                      SUMMARIZE ( 'Original Table', 'Original Table'[Year-Qtr] ),
                      'Original Table'[Year-Qtr] = "202304"
                  ),
                  CALCULATE ( SUM ( 'Original Table'[Units] ) ) >= 1
              )
          ) = 1
      RETURN
          IF ( _conditionone && _conditiontwo, "Yes" )
      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        2 years ago

        Hi,

        If you have only one table in your semantic model, please try something like below.

         

        Resurrect CC =
VAR _conditionone =
    SUMX (
        FILTER (
            'Original Table',
            'Original Table'[Name] = EARLIER ( 'Original Table'[Name] )
                && 'Original Table'[Year-Qtr] = "202303"
        ),
        'Original Table'[Units]
    ) = 1
VAR _conditiontwo =
    SUMX (
        FILTER (
            'Original Table',
            'Original Table'[Name] = EARLIER ( 'Original Table'[Name] )
                && 'Original Table'[Year-Qtr] = "202304"
        ),
        'Original Table'[Units]
    ) >= 1
RETURN
    IF ( _conditionone && _conditiontwo, "Yes" )