cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FaerchDK
Frequent Visitor

Disable choice option if there is x number of items with that choice

Hi

 

I have a SharePoint list [fdsfsa] with a choice [Date] column.

The choice column has 5 choice options.

 

In Power Apps I want to disable each choice option which have more than 49 items with that choice option.

 

I can disable one choice [CW4: 24th – 28th January 2022] with the code below.

But if I need to code all the combinations it's gonna be a very long formula.

 

Is there an easier way to do this?

 

 

 

If(
    CountRows(
        Filter(
            fdsfsa,
            Date.Value = "CW4: 24th – 28th January 2022"
        )
    ) > 49,
    Filter(
        Choices([@fdsfsa].Date),
        Value <> "CW4: 24th – 28th January 2022"
    ),
    Choices(fdsfsa.Date)
)

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Super User
Super User

Hi @FaerchDK 

 

Please try this

 

Filter(
    Choices([@fdsfsa].Date),
    Not(
        Value in ( Filter(
            AddColumns(
                GroupBy(
                    AddColumns(
                        [@fdsfsa],
                        "DateValue",
                        Date.Value
                    ),
                    "DateValue",
                    "GroupName"
                ),
                "Count",
                CountRows(GroupName)
            ),
            Count > 49
        ).DateValue)
    )
)

 


Thanks,
Stalin - Learn To Illuminate

View solution in original post

3 REPLIES 3
StalinPonnusamy
Super User
Super User

Hi @FaerchDK 

 

Please try this

 

Filter(
    Choices([@fdsfsa].Date),
    Not(
        Value in ( Filter(
            AddColumns(
                GroupBy(
                    AddColumns(
                        [@fdsfsa],
                        "DateValue",
                        Date.Value
                    ),
                    "DateValue",
                    "GroupName"
                ),
                "Count",
                CountRows(GroupName)
            ),
            Count > 49
        ).DateValue)
    )
)

 


Thanks,
Stalin - Learn To Illuminate

Thanks @StalinPonnusamy 

 

I'm not quite sure on how the formula works. But it works as intended.

FaerchDK
Frequent Visitor

Hi @StalinPonnusamy can you help again with a similar issue?

 

I need to filter the choice options from the SharePoint list lookup column 'Location'.

I want to filter based on the following criteria

 

  • Regardles of the checkbox 'chkEmptyLocations'. Filter the string based on three comboxes (cboLocationFilter, cboRowFilter & cboPostitionFilter) 
  • Only if the checkbox 'chkEmptyLocations' is true. Filter out choices if there are other items in the list with that Location, except if the column 'More than one blade' is equal to 1

SharePoint list: 'Blade Status'

Choice column: 'Location'

String column: 'More than one blade'

 

With the help you gave me for my other issue, I have come up with the code below.

The only thing missing is to keep the choices where the column 'More than one blade' is equal to 1

 

Can you help?

 

 

If(
    chkEmptyLocations.Value = true,
    Filter(
        Choices(
            [@'Blade Status'].Location,
            Trim(cboLocationFilter.Selected.Value & " " & cboRowFilter.Selected.Result & " " & cboPositionFilter.Selected.Position)
        ),
        Not(
            Id in (Filter(
                AddColumns(
                    GroupBy(
                        AddColumns(
                            [@'Blade Status'],
                            "LocationID",
                            Location.Id
                        ),
                        "LocationID",
                        "GroupName"
                    ),
                    "Count",
                    CountRows(GroupName)
                ),
                Count > 0
            ).LocationValue)
        )
    ),
    Choices(
        [@'Blade Status'].Location,
        Trim(cboLocationFilter.Selected.Value & " " & cboRowFilter.Selected.Result & " " & cboPositionFilter.Selected.Position)
    )
)

 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,541)