Count if at least one value meets criteria then set new column to true

[Anonymous]

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.

[amitchandak · Accepted solution]

@Anonymous , the grouping is not clear you need something like

 if(countx(filter(Table,[Mapeamento] =earlier([Mapeamento]) && [Valor] = "Sim"),[Mapeamento] ) >0, "Yes","No")

[mahoneypat]

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

[BA_Pete]

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

[amitchandak · Accepted solution]

@Anonymous , the grouping is not clear you need something like

 if(countx(filter(Table,[Mapeamento] =earlier([Mapeamento]) && [Valor] = "Sim"),[Mapeamento] ) >0, "Yes","No")

[Anonymous · Accepted solution]

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

[Fowmy]

@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