Forum Discussion

Audrey_ADAPEI35's avatar
Audrey_ADAPEI35
Frequent Visitor
2 years ago
Solved

Matrix and database in POWER BI

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.

ANNEEMOISDOSSIER_PAIEMATRICULEABSENCEDEBUT_ARRETFIN_ARRETNB_JOURSCATEGORIE_ARRET
20231X-XXYYY1234Maladie15/01/202325/06/202316Arrêt long (de 8 à 30 jours) 
20232X-XXYYY1234Maladie15/01/202325/06/202328Arrêt long (de 30 à 90 jours) 
20233X-XXYYY1234Maladie15/01/202325/06/202331Arrêt long (de 30 à 90 jours) 
20234X-XXYYY1234Maladie15/01/202325/06/202330Arrêt long (> 90 jours) 
20235X-XXYYY1234Maladie15/01/202325/06/202331Arrêt long (> 90 jours) 
20236X-XXYYY1234Maladie15/01/202325/06/202325Arrê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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

3 Replies

  • mickey64's avatar
    mickey64
    Super User
    2 years ago

    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.

     

  • Audrey_ADAPEI35's avatar
    Audrey_ADAPEI35
    Frequent Visitor
    2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.