Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
turcelaygoy
Helper I
Helper I

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 wondering if there is any way of programing loops so I can run the database and see the acumulated variation that is happening through those units of products. 

 

Example:

Right now I'm able to calculate the variation between units 5 and 10 doing: (Number of faults in unit 10-Number of faults in unit 5)/Number of faults in unit 5*100

 

But I would like to calculate the variation taking into account all the units of products that are between 5 and 10; 5,6,7,8,9 and 10.

 

I hope someone can help me with this problem 🙂

 

Thank you!

1 ACCEPTED SOLUTION

Hi @turcelaygoy 
I think now is correct.

1.png

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

View solution in original post

23 REPLIES 23
devanshi
Helper V
Helper V

VariationBetweenUnits = VAR StartUnit = 5
VAR EndUnit = 10
VAR NumUnits = EndUnit - StartUnit + 1
VAR StartFaults = CALCULATE(SUM('Table'[Number of Faults]), 'Table'[Unit] = StartUnit)
VAR EndFaults = CALCULATE(SUM('Table'[Number of Faults]), 'Table'[Unit] = EndUnit)
VAR VariationSum = SUMX( GENERATESERIES(StartUnit, EndUnit-1, 1),
                                               ABS(CALCULATE(SUM('Table'[Number of Faults]),
                                  'Table'[Unit] = EARLIER(StartUnit)) - CALCULATE(SUM('Table'[Number of Faults]),
                                                   'Table'[Unit] = EARLIER(StartUnit) + 1)) )
RETURN
DIVIDE(VariationSum, StartFaults) * 100

turcelaygoy
Helper I
Helper I

nlka

@turcelaygoy 
Thsnk you! Now the picture is more clear. However, you still did not expalin what is the problem. What do you mean by "It returns the variation between the initial unit and the (final unit-1)."? Please elaborate on this with one example and advise what should be the correct result.

I have chosen project Oslo and initial UT 30 and final UT 32 and it returns me the variation between UT 30 and 33 that is 655% but it should say -67% (these results have been calculated filtering the variables in the excel sheet of the database). But then I have tried different projects and the variation that it returns is not correct. So right now the measure gives a result but not correct.

@turcelaygoy 

Can you share a sample file?

of the excel sheet or the power BI

 

Power Bi

It doesn't let me share the file

 

@turcelaygoy 
You can only share a download link.

Link

Does this link work? @tamerj1 

@turcelaygoy 

Yes it worked and I have downloaded the file but still didn't have the chance to look at it. 

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

No problem!!

Hi @turcelaygoy 
I think now is correct.

1.png

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

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

Thank you very much!!

ppm1
Solution Sage
Solution Sage

Have you tried to create a table variable in a measure? For example,

 

VAR UnitTable = ADDCOLUMNS(VALUES(Table[Unit]), "YourMeasure", [YourMeasure])

RETURN Min, Max, Median, Avg calculation

 

If you want to specify only a fixed subset of units, you can wrap the ADDCOLUMNS expression with CALCULATETABLE(ADDCOLUMNS(...), Table[Unit] IN {5,6,7,8,9,10} )

 

Pat

 

Microsoft Employee
turcelaygoy
Helper I
Helper I

It works but the variation that it ruturns is not the one according to the numbers selected. It returns the variation between the initial unit and the (final unit-1).

@turcelaygoy 
I believe I need to see some screenshots. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors