Forum Discussion

turcelaygoy's avatar
turcelaygoy
Helper I
3 years ago
Solved

Possible loop in DAX

Hello!   I was trying to calculate the variation of some faults in the different units of products and I have been able to see the variation between to independent units of products. I was wonderin...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi turcelaygoy 
    I think now is correct.

    Filtered variation_2 = 
AVERAGEX (
    CROSSJOIN (
        CROSSJOIN ( VALUES ( 'UT_INII'[UT1] ), VALUES ( 'UT_FIN'[UT2] ) ),
        VALUES ( 'Tabla Proyecto'[PROYECTO] )
    ),
    VAR T =
        CALCULATETABLE ( Hoja1 )
    RETURN
        AVERAGEX (
            GENERATESERIES (  [UT1], [UT2] - 1, 1 ),
            VAR CurrentFaults =
                SUMX ( FILTER ( T, Hoja1[Columna] = [Value] ), Hoja1[FALTAS TOTALES (QA)] )
            VAR NextFaults =
                SUMX ( FILTER ( T, Hoja1[Columna] = [Value] + 1 ), Hoja1[FALTAS TOTALES (QA)] )
            RETURN
                DIVIDE ( NextFaults - CurrentFaults, CurrentFaults ) * 100
        )
)
tamerj1's avatar
tamerj1
Community Champion
3 years ago

turcelaygoy 

So sorry. I was so busy with other stuff and didn't have the chance to look into it. Today I'm out of office. I will check on Monday morning. 

  • turcelaygoy's avatar
    turcelaygoy
    Helper I
    3 years ago

    No problem!!

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi turcelaygoy 
    I think now is correct.

    Filtered variation_2 = 
AVERAGEX (
    CROSSJOIN (
        CROSSJOIN ( VALUES ( 'UT_INII'[UT1] ), VALUES ( 'UT_FIN'[UT2] ) ),
        VALUES ( 'Tabla Proyecto'[PROYECTO] )
    ),
    VAR T =
        CALCULATETABLE ( Hoja1 )
    RETURN
        AVERAGEX (
            GENERATESERIES (  [UT1], [UT2] - 1, 1 ),
            VAR CurrentFaults =
                SUMX ( FILTER ( T, Hoja1[Columna] = [Value] ), Hoja1[FALTAS TOTALES (QA)] )
            VAR NextFaults =
                SUMX ( FILTER ( T, Hoja1[Columna] = [Value] + 1 ), Hoja1[FALTAS TOTALES (QA)] )
            RETURN
                DIVIDE ( NextFaults - CurrentFaults, CurrentFaults ) * 100
        )
)
  • turcelaygoy's avatar
    turcelaygoy
    Helper I
    3 years ago

    Thank you very much!!

  • turcelaygoy's avatar
    turcelaygoy
    Helper I
    3 years ago

    Hello tamerj1 !!!

    I have another doubt ;). I would like to see the variation of the faults between the different "Coches"(it means cars in Spanish) of each project. I want to have the choice of the variables "Proyecto" and "Area" in two different data segmentations and see the variation of the whole project but calculating first the variation in each "Coche" and then doing the average of all.

     

    To do this, I need to first order the "Coches" that have been produced. I have thought of doing a new column setting numbers in order to the first date of inspection "Fecha 1ª Inspeccion" of each "Coches", but I cannot manage to create that column.

     

    After this I would do the variation of the faults in each car using the code you provided and after that the average of all the variations of the different "Coches".