Look for value in subgroup, then put true/false in column for entire subgroup.

[Phobos1]

Hi all, hope someone can help me out with a formula.

My dataset looks like the table below, now I am trying to create the 'New column' with a formula but can't get it to work right. In the dataset there are different sorts of fruit, they can have a value of 0, 1, 2 or 3. The rule I need to implement is: If a fruit has a zero in the 'count' column for any of it's entries, then the result in 'New column' should be false for all entries of this fruit. Else, it should be true. A fruit can have only one entry, or multiple. I came up with this formula but is doesn't work:

Newcolumn=CALCULATE(IF(MIN('Data fruit' [Count])=0,0,sum('Data fruit'[Count])), FILTER(ALLSELECTED('Data fruit'),'Data fruit' [Name fruit]=EARLIER('Data fruit' [Name fruit])))

[Phobos1]

This works excellently, it does exactly what I need it to do. Thanks so much for solving this!

[Anonymous · Accepted solution]

Hi @Phobos1 , 

Please refer to my pbix file to see if it help you.

Create a column.

new column =
VAR _min =
    CALCULATE (
        MIN ( 'Table'[Count] ),
        FILTER ( 'Table', 'Table'[Fruit] = EARLIER ( 'Table'[Fruit] ) )
    )
RETURN
    IF ( _min = 0, FALSE (), TRUE () )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

define table Tab = 
SELECTCOLUMNS(
    {
        ("Pear", 2),
        ("Pear", 3),
        ("Pear", 0),
        ("Orange", 1),
        ("Orange", 2),
        ("Cherry", 0)
    },
    "Fruit", [Value1],
    "Count", [Value2]
)
EVALUATE
    ADDCOLUMNS(
        Tab,
        "Your Column",
            var currentFruit = Tab[Fruit]
            var fruitHasNoZeros =
                ISEMPTY(
                    FILTER(
                        Tab,
                        Tab[Fruit] = currentFruit
                        && Tab[Count] = 0
                    )
                )
            return
                fruitHasNoZeros
    )

Run this in DAX Studio, for example. The formula for "Your Column" will do what you want.

[rbriga]

The simplest way to achieve this is:

Use this table in PowerQuery to group each fruit by minimum of count.

Here's an example:

let
    Source = Excel.CurrentWorkbook(){[Name="Fruits"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Fruit"}, {{"Min", each List.Min([Count]), type nullable number}})
in
    #"Grouped Rows"

This table will be hidden from the model.

Now use PowerQuery again to flag those Min=0 fruits:

let
    Source = Excel.CurrentWorkbook(){[Name="Fruits"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Count", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Fruit"}, MinCount, {"Fruit"}, "MinCount", JoinKind.LeftOuter),
    #"Expanded MinCount" = Table.ExpandTableColumn(#"Merged Queries", "MinCount", {"Min"}, {"Min"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded MinCount", "Test", each if [Min] = 0 then false else true, type logical),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Min"})
in
    #"Removed Columns"

It's not the cleanest way, but it's better than calculated columns.

[Phobos1]

Thanks for the quick reply. I tried to make this work but run into some issues as the fruit and count columns are part of a larger dataset. Is there a way to do something like partition by + rank&select in powerBI and add a calculated column with this to the dataset?