Forum Discussion
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 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 :
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.
6 Replies
Hi Rac3rss - If possible can you please convert to english language?
Helpful to understand.
Thank you
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.
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.
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!!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
