Columna calculada

[Rac3rss]

Hola,

Necesito crear una columna calculda en DAX pero no encuentro la forma más apropiada. Si bien es cierto que en Excel dispongo de ella sin problema, en Power BI me es bastante dificil.

Mi tabla principal es la siguiente:

Con	Con futur
S-3	S-23
S-5	S-67
S-23
S-544	S-78
S-43
S-67	S-12
S-12
S-78

Necesito encontrar el con anterior y asignarle un ID para cada grupo de con. La tabla resultante en excel seria de esta manera:

Con	Con futur	Con anterior	ID
S-3	S-23	No	S-3
S-5	S-67	No	S-5
S-23		S-3	S-3
S-544	S-78	No	S-544
S-43		No	S-43
S-67	S-12	S-5	S-5
S-12		S-67	S-5
S-78		S-544	S-544

Donde con anterior en Excel es : =SIERROR(INDEX($G$2:$G$9;COINCIDEIX(G2; $H$2:$H$9; 0)); "No")

Donde el ID es : =+SI(I2="No";G2;CONSULV(I2;G:J;4;0))

En power BI - DAX;

el con anterior es : 

Con anterior =

VAR result =

    LOOKUPVALUE(

        contractes[Contracte],

        contractes[Con futur], contractes[Contracte]

    )

RETURN

    IF(ISBLANK(result), "No hi ha", result)

Y el ID no tengo forma de sacarlo.

Alguien me podría ayudar por favor?

Muchas gracias.

[v-jianpeng-msft · Accepted solution]

Thank you @rajendraongole1 

Hi, @Rac3rss 

Based on the sample data you provided, I used the following sample data:

I created a calculated column using the following DAX:

Column = 
VAR _table = SUMMARIZE(ALL('Table'),'Table'[Con ],'Table'[Con futur])
VAR _con = 'Table'[Con ]
VAR _Confutur = MAXX(FILTER(_table,'Table'[Con futur]=_con),'Table'[Con futur])
RETURN IF(MAXX(FILTER(_table,'Table'[Con futur]=_con),'Table'[Con futur])<>BLANK(),MAXX(FILTER(_table,'Table'[Con futur]=_Confutur),'Table'[Con ]),"No")

I obtained the final result by creating a calculated column using the following DAX expression:

Result = 
VAR _table = SUMMARIZE(ALL('Table'),'Table'[Con ],'Table'[Con futur])
VAR _con = 'Table'[Column]
VAR _Confutur = MAXX(FILTER(_table,'Table'[Con futur]=_con),'Table'[Con futur])
RETURN IF('Table'[Column]="No",'Table'[Con ],IF(MAXX(FILTER(_table,'Table'[Con futur]=_con),'Table'[Con ])<>BLANK(),MAXX(FILTER(_table,'Table'[Con futur]=_con),'Table'[Con ]),'Table'[Column]))

I have provided the PBIX file used in this instance below.

Best Regards

Jianpeng Li

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

[Rac3rss]

I need to create a calculated column in DAX, but I can't find the most appropriate way. While it's true that in Excel I have it without any problem, in Power BI it is quite difficult for me.

My main table is as follows:

Con Con futur

S-3	S-23
S-5	S-67
S-23
S-544	S-78
S-43
S-67	S-12
S-12
S-78

I need to find the "con anterior" (previous con) and assign an ID to each group of con. The resulting table in Excel would look like this:

Con Con futur Con anterior ID

S-3	S-23	No	S-3
S-5	S-67	No	S-5
S-23		S-3	S-3
S-544	S-78	No	S-544
S-43		No	S-43
S-67	S-12	S-5	S-5
S-12		S-67	S-5
S-78		S-544	S-544

Where "con anterior" in Excel is:

=IFERROR(INDEX($G$2:$G$9, MATCH(G2, $H$2:$H$9, 0)), "No")

And where the ID is:

=IF(I2="No", G2, VLOOKUP(I2, G:J, 4, FALSE))

In Power BI - DAX;

"con anterior" is:

Con anterior = VAR result = LOOKUPVALUE( contractes[Contracte], contractes[Con futur], contractes[Contracte] ) RETURN IF(ISBLANK(result), "No hi ha", result)

And I can't find a way to get the ID.

Could someone please help me?

Thank you very much.

[rajendraongole1]

Hi @Rac3rss - Thanks for the conversion!

create a calculated column will look up the previous Con based on the Con futur fields

Con anterior =
VAR result = LOOKUPVALUE(
Table1[Con],
Table1[Con futur],
Table1[Con]
)
RETURN IF(ISBLANK(result), "No", result)

Create sequence number for ID calculated column as below:

ID =

VAR ConValue = _Conds[Con]

VAR ConAnteriorValue = _Conds[Con anterior]

VAR ResultID =

    IF(

        ConAnteriorValue = "No",

        ConValue,

        CALCULATE(

            MAX(_Conds[Con anterior]),

            FILTER(

                _Conds,

                _Conds[Con] = ConAnteriorValue

            )

        )

    )

RETURN IF(ISBLANK(ResultID), ConValue, ResultID)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Rac3rss]

Hello, I'm sorry, but the answer is not correct. The resulting column should be as follows.

ID

S-3

S-5

S-3

S-544

S-43

S-5

S-5

S-544

[rajendraongole1]

Hi @Rac3rss - Ok, No need to change the first calculation.

Con anterior =

VAR result = LOOKUPVALUE(Concif[Con], Concif[Con futur], Concif[Con])

RETURN IF(ISBLANK(result), "No", result)

Adjust your second calculated column id as below:

ID =

VAR CurrentCon = Concif[Con]

VAR StartCon = CurrentCon

VAR MaxIterations = 100

VAR Result =

    GENERATE(

        GENERATESERIES(1, MaxIterations, 1),

        VAR Iteration = [Value]

        VAR PreviousCon =

            IF(Iteration = 1, CurrentCon,

                LOOKUPVALUE(Concif[Con anterior], Concif[Con], CurrentCon)

            )

        RETURN

            ROW(

                "Iteration", Iteration,

                "PreviousCon", PreviousCon,

                "CurrentCon", CurrentCon

            )

    )

VAR FinalCon =

    MAXX(

        FILTER(

            Result,

            [PreviousCon] = "No"

        ),

        [CurrentCon]

    )

RETURN

    IF(ISBLANK(FinalCon), StartCon, FinalCon)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[rajendraongole1]

Hi @Rac3rss - If possible can you please convert to english language?

Helpful to understand.

Thank you