Forum Discussion

Rac3rss's avatar
Rac3rss
Icon for Helper I rankHelper 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.

     

     

     

Rac3rss's avatar
Rac3rss
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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!!