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.

     

     

     

Rac3rss's avatar
Rac3rss
Helper I
2 years ago

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-3S-23
S-5S-67
S-23 
S-544S-78
S-43 
S-67S-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-3S-23NoS-3
S-5S-67NoS-5
S-23 S-3S-3
S-544S-78NoS-544
S-43 NoS-43
S-67S-12S-5S-5
S-12 S-67S-5
S-78 S-544S-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'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!!