Forum Discussion

bonjourposte's avatar
bonjourposte
Helper V
1 year ago
Solved

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter exp

Hi all, I'm getting this error message with my measure, trying to figure out the number of Renewals and Extensions we've done in the past year.  What does it want me to change?
 

 


 


 

 
NumRenewals2 =
VAR MatDate = MAX(LOANHISTSorted[MAT_DATE])
VAR CurrentLoanStatus = MAX(LOANHISTSorted[LOAN_STATUS])
VAR CurrentLoanTerm = MAX(LOANHISTSorted[CURR_LOAN_TERM])
VAR CurrentAmzMos = MAX(LOANHISTSorted[CURR_AMZ_MONTHS])

VAR PREV_MAT_DATE = MAX(LOANHISTSorted[PREV_MAT_DATE])
VAR PREV_LOAN_STATUS = MAX(LOANHISTSorted[PREV_LOAN_STATUS])
VAR PREV_LOAN_TERM = MAX(LOANHISTSorted[PREV_LOAN_TERM])
VAR PREV_AMZ_MOS = MAX(LOANHISTSorted[PREV_AMZ_MONTHS])
VAR PREV_START_DATE = MAX(LOANHISTSorted[PREV_START_DATE])



RETURN
CALCULATE(
    COUNTROWS(LOANHISTSorted),
        IF(
            AND(
                MatDate <> [PreviousMatDate],
                CurrentLoanStatus <> "Commitment" &&
                PREV_LOAN_STATUS <> "Cancelled"&&
                PREV_LOAN_STATUS <> "PAIDINFULL"
            ),
        SWITCH(
            TRUE,
            PREV_START_DATE > MatDate, "Renewal",
            PREV_LOAN_TERM <> CurrentLoanTerm, PREV_AMZ_MOS = CurrentAmzMos, "Extension"
            )
        )
    )
Need Help
  • PijushRoy's avatar
    PijushRoy
    1 year ago

    Hi bonjourposte 
    Can you please check the below DAX, please let me know if not works

    NumRenewals2 = 
VAR MatDate = MAX(LOANHISTSorted[MAT_DATE])
VAR CurrentLoanStatus = MAX(LOANHISTSorted[LOAN_STATUS])
VAR CurrentLoanTerm = MAX(LOANHISTSorted[CURR_LOAN_TERM])
VAR CurrentAmzMos = MAX(LOANHISTSorted[CURR_AMZ_MONTHS])

VAR PREV_MAT_DATE = MAX(LOANHISTSorted[PREV_MAT_DATE])
VAR PREV_LOAN_STATUS = MAX(LOANHISTSorted[PREV_LOAN_STATUS])
VAR PREV_LOAN_TERM = MAX(LOANHISTSorted[PREV_LOAN_TERM])
VAR PREV_AMZ_MOS = MAX(LOANHISTSorted[PREV_AMZ_MONTHS])
VAR PREV_START_DATE = MAX(LOANHISTSorted[PREV_START_DATE])

RETURN
    CALCULATE(
        COUNTROWS(LOANHISTSorted),
        FILTER(
            LOANHISTSorted,
            MatDate <> PREV_MAT_DATE &&
            CurrentLoanStatus <> "Commitment" &&
            PREV_LOAN_STATUS <> "Cancelled" &&
            PREV_LOAN_STATUS <> "PAIDINFULL" &&
            (
                (PREV_START_DATE > MatDate) || 
                (PREV_LOAN_TERM <> CurrentLoanTerm && PREV_AMZ_MOS = CurrentAmzMos)
            )
        )
    )

     

3 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    1 year ago

    Hi bonjourposte 
    Can you please check the below DAX, please let me know if not works

    NumRenewals2 = 
VAR MatDate = MAX(LOANHISTSorted[MAT_DATE])
VAR CurrentLoanStatus = MAX(LOANHISTSorted[LOAN_STATUS])
VAR CurrentLoanTerm = MAX(LOANHISTSorted[CURR_LOAN_TERM])
VAR CurrentAmzMos = MAX(LOANHISTSorted[CURR_AMZ_MONTHS])

VAR PREV_MAT_DATE = MAX(LOANHISTSorted[PREV_MAT_DATE])
VAR PREV_LOAN_STATUS = MAX(LOANHISTSorted[PREV_LOAN_STATUS])
VAR PREV_LOAN_TERM = MAX(LOANHISTSorted[PREV_LOAN_TERM])
VAR PREV_AMZ_MOS = MAX(LOANHISTSorted[PREV_AMZ_MONTHS])
VAR PREV_START_DATE = MAX(LOANHISTSorted[PREV_START_DATE])

RETURN
    CALCULATE(
        COUNTROWS(LOANHISTSorted),
        FILTER(
            LOANHISTSorted,
            MatDate <> PREV_MAT_DATE &&
            CurrentLoanStatus <> "Commitment" &&
            PREV_LOAN_STATUS <> "Cancelled" &&
            PREV_LOAN_STATUS <> "PAIDINFULL" &&
            (
                (PREV_START_DATE > MatDate) || 
                (PREV_LOAN_TERM <> CurrentLoanTerm && PREV_AMZ_MOS = CurrentAmzMos)
            )
        )
    )

     

    • bonjourposte's avatar
      bonjourposte
      Helper V
      1 year ago

      Hi PijushRoy , I got it to work!  Thanks for your help.

       

      NumRenewals =

      VAR MatDate = MAX(LOANHISTSorted[MAT_DATE])

      VAR PREV_START_DATE = MAX(LOANHISTSorted[PREV_START_DATE])

      VAR CurrentLoanStatus = MAX(LOANHISTSorted[LOAN_STATUS])

       

      RETURN

      CALCULATE(

          COUNTROWS(LOANHISTSorted),

          FILTER(

                  LOANHISTSorted,

                      LOANHISTSorted[MAT_DATE] <> LOANHISTSorted[PREV_MAT_DATE] &&

                      CurrentLoanStatus <> "Commitment" &&

                      LOANHISTSorted[PREV_LOAN_STATUS] <> "Cancelled" &&

                      LOANHISTSorted[PREV_LOAN_STATUS] <> "PAIDINFULL" &&

                      LOANHISTSorted[CURR_START_DATE] > LOANHISTSorted[PREV_MAT_DATE])

    • bonjourposte's avatar
      bonjourposte
      Helper V
      1 year ago

      Thanks, it didn't work, but it's the closest I've gotten.  

       

      I took out the line about Extensions because I'll do the Extensions in another matrix.  This one will just be Renewals.  

        

       

      It should look like the first matrix, but it gives me the second one.

       

       
      NumRenewals2 =
      VAR MatDate = MAX(LOANHISTSorted[MAT_DATE])
      VAR CurrentLoanStatus = MAX(LOANHISTSorted[LOAN_STATUS])
      VAR CurrentLoanTerm = MAX(LOANHISTSorted[CURR_LOAN_TERM])
      VAR CurrentAmzMos = MAX(LOANHISTSorted[CURR_AMZ_MONTHS])

       

      VAR PREV_MAT_DATE = MAX(LOANHISTSorted[PREV_MAT_DATE])
      VAR PREV_LOAN_STATUS = MAX(LOANHISTSorted[PREV_LOAN_STATUS])
      VAR PREV_LOAN_TERM = MAX(LOANHISTSorted[PREV_LOAN_TERM])
      VAR PREV_AMZ_MOS = MAX(LOANHISTSorted[PREV_AMZ_MONTHS])
      VAR PREV_START_DATE = MAX(LOANHISTSorted[PREV_START_DATE])




      RETURN
      CALCULATE(
          COUNTROWS(LOANHISTSorted),
          FILTER(
                  LOANHISTSorted,
                      MatDate <> PREV_MAT_DATE &&
                      CurrentLoanStatus <> "Commitment" &&
                      PREV_LOAN_STATUS <> "Cancelled" &&
                      PREV_LOAN_STATUS <> "PAIDINFULL" &&
                      PREV_START_DATE > MatDate
              )
      )
Related content