Forum Discussion

yamina's avatar
yamina
Icon for Helper I rankHelper I
1 year ago
Solved

Help dax while adding column

Hi    I need to duplicate these two excel formulas in power bi deskstop by adding two news colums in my table  Can you help Me ?    Date_creation_DACRI N_dossier_NUDOS N_prestation_RGOUV M...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi yamina ,

     

    Please try following Dax formulars.

    Min date = 
VAR CurrentIndex = 'Table'[Index]
VAR PreviousDossier = MAXX(FILTER('Table', 'Table'[Index] = CurrentIndex -1), 'Table'[N_dossier_NUDOS])
VAR CurrentDossier = 'Table'[N_dossier_NUDOS]
RETURN
IF(
    PreviousDossier = CurrentDossier && CurrentDossier <> BLANK(),
    MINX(FILTER('Table', 'Table'[N_dossier_NUDOS] = CurrentDossier), 'Table'[Date_creation_DACRI]),
    BLANK()
)
    Condition retour = 
VAR CurrentDossier = 'Table'[N_dossier_NUDOS]
VAR CurrentIndex = 'Table'[Index]
VAR FirstDossierIndex = MINX(FILTER('Table', 'Table'[N_dossier_NUDOS] = CurrentDossier),'Table'[Index])
VAR FirstDossierCreationDate = CALCULATE(SELECTEDVALUE('Table'[Date_creation_DACRI]), 'Table'[Index] = FirstDossierIndex , ALL())
VAR CurrentPresentation = 'Table'[N_prestation_RGOUV]
VAR PreviousPresentation =  MAXX(FILTER('Table', 'Table'[Index] = CurrentIndex -1), 'Table'[N_prestation_RGOUV])
RETURN

IFERROR(
    IF( 'Table'[Date_creation_DACRI] - FirstDossierCreationDate >= 0 && 'Table'[Date_creation_DACRI] - FirstDossierCreationDate <= 30 && CurrentPresentation >= PreviousPresentation +1,
        "Retour dans les 30 jours",
        BLANK()
    ),
    BLANK()
)

     

    The final result is as follows. Hopefully it will meet your needs.

     

    Please see the attached pbix for reference.

     

    Best Regards,
    Dengliang Li

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

yamina's avatar
yamina
Icon for Helper I rankHelper I
1 year ago

hi rajendraongole1 

See the error message when I try your solution. The Min date is ok but the condition retour returns this error  " Les opérations DAX de comparaison ne prennent pas en charge la comparaison des valeurs de type Text avec des valeurs de type Number. Utilisez la fonction VALUE ou FORMAT pour convertir une des valeurs." because the N_dossier is in text format. 

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

Hi yamina - can you try the modified one.

Condition retour =
VAR MinDate = 'YourTable'[Min date]
VAR CurrentDate = 'YourTable'[Date_creation_DACRI]
VAR CurrentDossier = 'YourTable'[N_dossier_NUDOS] -- Ensure it's text
VAR CurrentPrestation = 'YourTable'[N_prestation_RGOUV]

VAR PreviousPrestation =
CALCULATE(
MAX('YourTable'[N_prestation_RGOUV]),
FILTER(
'YourTable',
FORMAT('YourTable'[N_dossier_NUDOS], "@") = FORMAT(CurrentDossier, "@") && -- Ensure text comparison
'YourTable'[Date_creation_DACRI] < CurrentDate
)
)

RETURN
IF(
NOT ISBLANK(MinDate) &&
CurrentDate - MinDate BETWEEN 0 AND 30 &&
NOT ISBLANK(PreviousPrestation) &&
CurrentPrestation > PreviousPrestation,
"Retour dans les 30 jours",
BLANK()
)

 

here, FORMAT(N_dossier_NUDOS, "@") to force text comparison.Ensured PreviousPrestation isn't blank before checking the condition.

  • yamina's avatar
    yamina
    Icon for Helper I rankHelper I
    1 year ago

    I try with this solution because beetween function didn't work 

    Condition retour =
    VAR MinDate = 'TDB 24 mois'[Min date]
    VAR CurrentDate = 'TDB 24 mois'[Date_creation_DACRe]
    VAR CurrentDossier = FORMAT('TDB 24 mois'[N_dossier_NUDOS], "@")
    VAR CurrentPrestation = 'TDB 24 mois'[N_prestation_RGOUV]

    VAR PreviousPrestation =
    CALCULATE(
        MAX('TDB 24 mois'[N_prestation_RGOUV]),
        FILTER(
            'TDB 24 mois',
            FORMAT('TDB 24 mois'[N_dossier_NUDOS], "@") = CurrentDossier &&
            'TDB 24 mois'[Date_creation_DACRe] < CurrentDate
        )
    )

    VAR IsMinDateValid = NOT ISBLANK(MinDate)
    VAR IsDateInRange = CurrentDate >= MinDate && CurrentDate <= MinDate + 30
    VAR IsPreviousPrestationValid = NOT ISBLANK(PreviousPrestation)
    VAR IsCurrentPrestationHigher = CurrentPrestation > PreviousPrestation

    RETURN
    IF(
        IsMinDateValid &&
        IsDateInRange &&
        IsPreviousPrestationValid &&
        IsCurrentPrestationHigher,
        "Retour dans les 30 jours",
        BLANK()
    )