Matrix and database in POWER BI

[Audrey_ADAPEI35]

Bonjour,

Dans le cadre d’une étude sur l’absentéisme dans mon entreprise, je souhaiterais catégoriser les arrêts selon leur durée. Les catégories sont les suivantes : « Arrêt court (<= 3j) », « Arrêt long (de 3 à 7 jours) », « Arrêt long (de 8 à 30 jours) », « Arrêt long (de 30 à 90 jours) » et « Arrêt long (> 90 jours) ».

Vous trouverez ci-dessous un exemple de base de données utilisée. Celle-ci découpe les arrêts mois par mois afin d’avoir un décompte mensuel du nombre de journées d’absence.

ANNEE	MOIS	DOSSIER_PAIE	MATRICULE	ABSENCE	DEBUT_ARRET	FIN_ARRET	NB_JOURS	CATEGORIE_ARRET
2023	1	X-XX	YYY1234	Maladie	15/01/2023	25/06/2023	16	Arrêt long (de 8 à 30 jours)
2023	2	X-XX	YYY1234	Maladie	15/01/2023	25/06/2023	28	Arrêt long (de 30 à 90 jours)
2023	3	X-XX	YYY1234	Maladie	15/01/2023	25/06/2023	31	Arrêt long (de 30 à 90 jours)
2023	4	X-XX	YYY1234	Maladie	15/01/2023	25/06/2023	30	Arrêt long (> 90 jours)
2023	5	X-XX	YYY1234	Maladie	15/01/2023	25/06/2023	31	Arrêt long (> 90 jours)
2023	6	X-XX	YYY1234	Maladie	15/01/2023	25/06/2023	25	Arrêt long (> 90 jours)

Le but est qu’un arrêt personne (ou matricule) soit compté une seule fois dans l’année d’étude et pour la valeur la plus élevée de catégorie d’arrêt qu’elle présente.

En effet, l’étude est filtrée à l’aide d’un segment de type curseur (« Entre ») et pour l’exemple ci-joint je souhaite qu’en sélectionnant « 1 » en mois max, la catégorie arrêt affiche « Arrêt long (de 8 à 30 jours) », en sélectionnant « 3 » en mois max, la catégorie arrêt affiche « Arrêt long (de 30 à 90 jours) » et en sélectionnant « 6 » en mois max, la catégorie arrêt affiche « Arrêt long (> 90 jours) ».

Après avoir testé plusieurs méthodes, en passant par Power Query, les colonnes calculées de Power BI, je ne parviens pas à réaliser cette manipulation.

Comment faire pour obtenir à la fin un tableau type matrice qui me donne le volume d’arrêts par type (arrêt court, arrêt long) par année en cumul et qui soit dynamique avec les filtres de date ?

Je vous remercie d’avance de votre aide.

[Audrey_ADAPEI35]

Thank you very much for your solution.

It works but the data in the matrix is not right because in our example, we choose to show one ID whose « arrêt » lasts many months.

For example, if my data shows information until January (slicer month = from january to january), the duration is equal to 16 then my matrix should look like this :

	2024
Arrêt long (de 8 à 30 jours)	1

If my data show information until February (slicer month = from january to February), the duration is equal to (16+28= 44) then my matrix should look like this :

	2024
Arrêt long (de 30 à 90 jours)	1

And so on… and with many more IDs.

We only want to show the category for each ID and « arrêt » that is the most recent with the slicer month (-> dynamic).

[v-rzhou-msft · Accepted solution]

Hi @Audrey_ADAPEI35 ,

I create a sample to have a test. Considering the New Year's Eve, I added a new material: ZZZ1234.

DimTables:

Dim Arrêt = 
DATATABLE(
    "Arrêt",STRING,
    "Order",INTEGER,
    "Arrêt Start",INTEGER,
    "Arrêt End",INTEGER,
    {
        {"Arrêt court (<= 3j)",1,,3},
        {"Arrêt long (de 3 à 7 jours)",2,4,7},
        {"Arrêt long (de 8 à 30 jours)",3,8,30},
        {"Arrêt long (de 30 à 90 jours)",4,31,90},
        {"Arrêt long (> 90 jours)",5,91,}
        }
)

DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"YearMonth",YEAR([Date])*100 + MONTH([Date]))

Data Model:

Measures:

MEASURE = 
VAR _YearMonthStart =
    MIN ( DimDate[YearMonth] )
VAR _YearMonthEnd =
    MAX ( DimDate[YearMonth] )
VAR _FILTER =
    FILTER (
        'Table',
        'Table'[ANNEE MOIS] >= _YearMonthStart
            && 'Table'[ANNEE MOIS] <= _YearMonthEnd
    )
VAR _SUMMARIZE =
    SUMMARIZE ( _FILTER, [MATRICULE], [ANNEE],"Sum", 
    VAR _ANNEE = [ANNEE]
    VAR _MATRICULE = [MATRICULE]
    RETURN
    SUMX (FILTER(_FILTER,[ANNEE] = _ANNEE && [MATRICULE] = _MATRICULE), [NB_JOURS] ) )
RETURN
    SWITCH (
        MAX ( 'Dim Arrêt'[Order] ),
        1, COUNTX ( FILTER ( _SUMMARIZE, [ANNEE] = MAX(DimDate[Year]) && [Sum] <= 3 ), [MATRICULE] ),
        2, COUNTX ( FILTER ( _SUMMARIZE, [ANNEE] = MAX(DimDate[Year]) && [Sum] > 3 && [Sum] <= 7 ), [MATRICULE] ),
        3, COUNTX ( FILTER ( _SUMMARIZE, [ANNEE] = MAX(DimDate[Year]) && [Sum] > 7 && [Sum] <= 30 ), [MATRICULE] ),
        4, COUNTX ( FILTER ( _SUMMARIZE, [ANNEE] = MAX(DimDate[Year]) && [Sum] > 30 && [Sum] <= 90 ), [MATRICULE] ),
        5, COUNTX ( FILTER ( _SUMMARIZE, [ANNEE] = MAX(DimDate[Year]) && [Sum] > 90 ), [MATRICULE] )
    ) + 0

Result is as below.

Best Regards,
Rico Zhou

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

[mickey64]

Step 0: I use these data tables below.

Step 1: I add a relationship.

Step 2: I add a 'C_Arret Value' column to the 'DATA' Table.

    C_Arret Value = RELATED(Arret[Value])

Step 3: I add a 'MOIS Table'.

    MOIS Table = SUMMARIZE('DATA','DATA'[MOIS])

Step 4: I make a slicer and a measure.

    M_MOIS_Sel = SELECTEDVALUE('MOIS Table'[MOIS])

Step 5: I make 2 measures and a Card visual.

    M_Arret Value_Max = CALCULATE(MAX('DATA'[C_Arret Value]),FILTER(ALLSELECTED('DATA'),'DATA'[MOIS]<=[M_MOIS_Sel]))

    M_Arret_Max = LOOKUPVALUE(Arret[Arret],'Arret'[Value],[M_Arret Value_Max])

Step 6: I make a matrix.