Forum Discussion

Rac3rss's avatar
Rac3rss
Helper I
2 years ago
Solved

Columna calculada

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...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

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's avatar
Rac3rss
Helper I
2 years ago

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's avatar
    rajendraongole1
    Super User
    2 years ago

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