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) », « A...
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.

     

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.