Help dax while adding column

[yamina]

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	Min date	Condition retour
10/04/2024	2003038	5	=SI(ET(I1=I2; I2<>""); MIN(SI(I:I=I2; A:A)); "")	=SIERREUR(SI(ET(A2 - L2 >= 0; A2 - L2 <= 30; J2 >= J1 + 1); "Retour dans les 30 jours"; "");"")
13/04/2024	7023525	1
10/04/2024	7190363	1
13/04/2024	7255522	3
09/04/2024	8254148	1
08/04/2024	8333347	1
10/04/2024	9226655	2
09/04/2024	9427691	3
11/04/2024	10143308	2
11/04/2024	10143309	4
13/04/2024	12189488	11
13/04/2024	12189488	11	13/04/2024
13/04/2024	12189488	11	13/04/2024
13/04/2024	12189488	11	13/04/2024

[yamina]

Sorry , 

I reformulate my request with more lines in the file. I would like to indicate for the same file number the return condition if the Presta number increases by at least 1 and if the creation date is within 30 days of the date of the 1st file

Date_creation_DACRI	N_dossier_NUDOS	N_prestation_RGOUV	Min date	Condition retour
10/04/2024	2003038	5	=SI(ET(I1=I2; I2<>""); MIN(SI(I:I=I2; A:A)); "")	=SIERREUR(SI(ET(A2 - L2 >= 0; A2 - L2 <= 30; J2 >= J1 + 1); "Retour dans les 30 jours"; "");"")
13/04/2024	29803685	2	13/04/2024	Retour dans les 30 jours
13/04/2024	29803685	3	13/04/2024	Retour dans les 30 jours
14/04/2024	29803685	4	13/04/2024	Retour dans les 30 jours
14/04/2024	29803685	5	13/04/2024	Retour dans les 30 jours
14/04/2024	29803685	6	13/04/2024	Retour dans les 30 jours
14/04/2024	29803685	7	13/04/2024	Retour dans les 30 jours
14/04/2024	29803685	8	13/04/2024	Retour dans les 30 jours
14/04/2024	29803685	9	13/04/2024	Retour dans les 30 jours
14/04/2024	29803685	10	13/04/2024	Retour dans les 30 jours
14/04/2024	29803685	11	13/04/2024	Retour dans les 30 jours
11/04/2024	37670929	3	11/04/2024	Retour dans les 30 jours
12/04/2024	38244974	12	08/04/2024	Retour dans les 30 jours
12/04/2024	47918139	3	12/04/2024	Retour dans les 30 jours
11/04/2024	48992688	14	11/04/2024	Retour dans les 30 jours
12/04/2024	48992688	15	11/04/2024	Retour dans les 30 jours
12/04/2024	48992688	16	11/04/2024	Retour dans les 30 jours
12/04/2024	48992688	18	11/04/2024	Retour dans les 30 jours
11/04/2024	51319279	11	10/04/2024	Retour dans les 30 jours
10/04/2024	51477461	6	09/04/2024	Retour dans les 30 jours
13/04/2024	51829240	4	10/04/2024	Retour dans les 30 jours
11/04/2024	53353783	4	09/04/2024	Retour dans les 30 jours
13/04/2024	53353783	5	09/04/2024	Retour dans les 30 jours
13/04/2024	54456236	5	09/04/2024	Retour dans les 30 jours
08/04/2024	54846145	4	08/04/2024	Retour dans les 30 jours
13/04/2024	54909754	5	09/04/2024	Retour dans les 30 jours

[Anonymous · Accepted solution]

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]

Hello 

As i understand I need to add an index column, but it takes a very long time because my table has 2 millions lines. so it is impossible. In addition I have to sort before the columns N_dossier_ num . and N_prestation_ RGOUV by asc. It is very heavy because my report use summarize  table from an another semantic model. so I don't know how to simplify this. 

[Anonymous]

Thanks for the reply from rajendraongole1, please allow me to provide another insight.

Hi @yamina ,

Please refers to the following steps.

Add an index column to the table in the Power Query editor.

Use the following DAX formulas to create the 'Min date' column and the 'Condition retour' column.

Min date = 
VAR CurrentIndex = [Index]
VAR PreviousDossier = MAXX(FILTER('Table', [Index] = CurrentIndex -1),[N_dossier_NUDOS])
VAR CurrentDossier = 'Table'[N_dossier_NUDOS]
RETURN
IF(
    PreviousDossier = CurrentDossier && CurrentDossier <> BLANK(),
    MINX(FILTER('Table', [N_dossier_NUDOS] = CurrentDossier), [Date_creation_DACRI]),
    BLANK()
)

Condition retour  = 
VAR CurrentIndex ='Table'[Index]
VAR CurrentPresentation = 'Table'[N_prestation_RGOUV]
VAR PreviousPresentation =  MAXX(FILTER('Table', [Index] = CurrentIndex -1), [N_prestation_RGOUV])
RETURN
IFERROR(
    IF( [Date_creation_DACRI] - [Min date] >=0  && [Date_creation_DACRI] - [Min date] <= 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 attahced 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.

[rajendraongole1]

Hi @yamina  - you can create two calculated columns using DAX.

Min date =
VAR CurrentDossier = 'YourTable'[N_dossier_NUDOS]
RETURN
IF(
COUNTROWS(FILTER('YourTable', 'YourTable'[N_dossier_NUDOS] = CurrentDossier)) > 1,
CALCULATE(MIN('YourTable'[Date_creation_DACRI]),
ALLEXCEPT('YourTable', 'YourTable'[N_dossier_NUDOS])
),
BLANK()
)

This column checks if the difference between Date_creation_DACRI and Min date is between 0 and 30, and if N_prestation_RGOUV has increased.

Condition retour =
VAR MinDate = 'YourTable'[Min date]
VAR CurrentDate = 'YourTable'[Date_creation_DACRI]
VAR CurrentDossier = 'YourTable'[N_dossier_NUDOS]
VAR CurrentPrestation = 'YourTable'[N_prestation_RGOUV]

VAR PreviousPrestation =
CALCULATE(
MAX('YourTable'[N_prestation_RGOUV]),
FILTER(
'YourTable',
'YourTable'[N_dossier_NUDOS] = CurrentDossier &&
'YourTable'[Date_creation_DACRI] < CurrentDate
)
)

RETURN
IF(
NOT ISBLANK(MinDate) &&
CurrentDate - MinDate >= 0 &&
CurrentDate - MinDate <= 30 &&
CurrentPrestation >= PreviousPrestation + 1,
"Retour dans les 30 jours",
BLANK()
)

replace with your table name as per the model. hope this helps.

[yamina]

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]

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]

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()

)