Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello folks,
In a table, besides other fields, I have 2 columns: one contains an attribute ([Atributo]) and the other contains its respective value ([Valor], "Sim" = Yes and "Não" = No). I need to create a new column that counts if there is at least one value [Valor] = "Sim" (Yes) based on the "cluster" of the column [Mapeamento]. For all values in [Mapeamento] that contains string "RAC 4", if there's at least one [Valor] = "Sim", I need to set the new column to "Sim" for all of its values. For the others, the value should be the same in [Valor]. Something like the below table:
Atributo | Valor | Mapeamento | New Column |
rac_102 | Sim | RAC 10_2 | Sim |
rac_101 | Sim | RAC 10_1 | Sim |
rac_9 | Não | RAC 9 | Não |
rac_62 | Sim | RAC 6_2 | Sim |
rac_61 | Não | RAC 6_1 | Não |
rac_5 | Sim | RAC 5 | Sim |
rac_45 | Não | RAC 4 | Sim |
rac_44 | Sim | RAC 4 | Sim |
rac_43 | Não | RAC 4 | Sim |
rac_42 | Sim | RAC 4 | Sim |
rac_41 | Não | RAC 4 | Sim |
rac_3 | Não | RAC 3 | Não |
rac_2 | Não | RAC 2 | Não |
rac_1 | Sim | RAC 1 | Sim |
Does anyone have any idea how it can be done?
Thanks in advance,
Thiago.
Solved! Go to Solution.
@Anonymous , the grouping is not clear you need something like
if(countx(filter(Table,[Mapeamento] =earlier([Mapeamento]) && [Valor] = "Sim"),[Mapeamento] ) >0, "Yes","No")
@amitchandak , @Fowmy :
I tried your formula but the result is "Sim" for all rows in the table.
I've made some changes and got to the expected result, thanks so much for all your help.
New_Valor =
if(
countx(
filter(
data;
data[Mapeamento] = earlier(data[Mapeamento]) &&
data[user_id] = earlier(data[user_id]) &&
data[Valor] = "Sim" &&
data[Mapeamento] = "RAC 4"
);
data[Mapeamento]);
"Sim";
data[Valor]
)
Thanks guys!!! You saved my day!
You can use an expression like this in your calculated column:
New Column =
IF (
ISBLANK (
CALCULATE (
COUNTROWS ( Table ),
ALLEXCEPT ( Table, Table[Mapeamento] ),
Table[Valor] = "Sim"
)
),
"Nao",
"Sim"
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous ,
If you want to push this work away from the data model then you can do this in Power Query as follows:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkpMjjc0MFLSUQrOzAWSQY7OCoYG8UZKsTowSUN0SUO4pCVQ0O/w4nyonCVcwgzVRDMkA80MUTWZIZlniqLLFC5uYoqqxwQhY4KiBUnCGKcWI1xaDHFpQTPLGC5hhCqBFGyogaYUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t, Mapeamento = _t]),
groupMapeamento = Table.Group(Source, {"Mapeamento"}, {{"data", each _, type table [Atributo=text, Valor=text, Mapeamento=text, New Column=text]}}),
addYourColumn = Table.AddColumn(groupMapeamento, "yourColumn", each if List.Contains(Table.Column([data], "Valor"), "Sim") then "Sim" else "Nao"),
expandOtherCols = Table.ExpandTableColumn(addYourColumn, "data", {"Atributo", "Valor"}, {"Atributo", "Valor"})
in
expandOtherCols
In Power Query, go to New Source > Blank Query then in Advanced Editor paste my code over the default code to follow the steps I took.
Pete
Proud to be a Datanaut!
@Anonymous , the grouping is not clear you need something like
if(countx(filter(Table,[Mapeamento] =earlier([Mapeamento]) && [Valor] = "Sim"),[Mapeamento] ) >0, "Yes","No")
@amitchandak , @Fowmy :
I tried your formula but the result is "Sim" for all rows in the table.
I've made some changes and got to the expected result, thanks so much for all your help.
New_Valor =
if(
countx(
filter(
data;
data[Mapeamento] = earlier(data[Mapeamento]) &&
data[user_id] = earlier(data[user_id]) &&
data[Valor] = "Sim" &&
data[Mapeamento] = "RAC 4"
);
data[Mapeamento]);
"Sim";
data[Valor]
)
Thanks guys!!! You saved my day!
@Anonymous
Add a new column:
SimPresent = IF(
CALCULATE(
COUNTROWS('Data'),ALLEXCEPT('Data','Data'[Mapeamento]), 'Data'[Valor]="Sim")>0,"Sim","Não")
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS ? to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |