cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
3csman
Impactful Individual
Impactful Individual

Add Condition to Formula

I've got the following in the Items of a Toggle control. The formula says, if 'All Items' in the Gallery show either Certified or Compliant the Toggle control should be set to 'true'.

 

ECMmbrStatusTgle_2

CountRows(ECMmbrCQSmryGallery_1.AllItems) = CountRows(Filter(ECMmbrCQSmryGallery_1.AllItems, ComplianceLabel_1.Text = "Compliant" || ComplianceLabel_1.Text = "Certified"))

 

I ALSO need to add a condition that says regardless of the first two conditions, check all Items and if 'Firefighter I' or 'Firefighter II' are not one of the 'CQTitle' in the Gallery, then Toggle control shall default to 'false' (Inactive) on start.

 

ComplianceLabel_1:

ComplianceLabel_1 has a formula that is reading the Gallery contents and returning the desire result to 

 

ECMmbrCQStatHtmlTxtFld_4:

"Compliance Status: "&If(ComplianceLabel_1.Text="Noncompliant","<font Color=Red>"," <font Color=BLUE>") & ComplianceLabel_1.Text

 

Example: With the formula above in the place the Toggle is appropriately automatically set. However, with the addition of the last condition the Toggle in this instance would flip to 'Inactive'(false), because there is no 'Firefighter I' or 'Firefighter II' in the Gallery.

 

 

Screen1.PNG

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @3csman :

I'm not sure if I understand your request, if there is an error, please help me point it out

When there is no record in ECMmbrCQSmryGallery_1 whose CQTitle value is "Firefighter I" or "Firefighter II", no matter what the result of 

 

CountRows(ECMmbrCQSmryGallery_1.AllItems) = CountRows(Filter(ECMmbrCQSmryGallery_1.AllItems, ComplianceLabel_1.Text = "Compliant" || ComplianceLabel_1.Text = "Certified"))

 

is, the value of the Toggle will be false.

If so,please try this code:

 

!IsBlank(LookUp(ECMmbrCQSmryGallery_1.AllItems,CQTitle="Firefighter I" || CQTitle="Firefighter II")) && 
 (CountRows(ECMmbrCQSmryGallery_1.AllItems) = CountRows(Filter(ECMmbrCQSmryGallery_1.AllItems, ComplianceLabel_1.Text = "Compliant" || ComplianceLabel_1.Text = "Certified")))

 

Best Regards,

Bof

View solution in original post

3 REPLIES 3
VijayTailor
Resident Rockstar
Resident Rockstar

Hi @3csman ,

if you don't have "CQTitle" as Lable in gallery then First add into the Gallery and you can hide it if you don't want to Show.

then Use the Condition in the same way as you are using previously.

Set the Default value based on Your Condition. Like you CQTitle <> what ever the txt contains in label .

Like below - 

CountRows(ECMmbrCQSmryGallery_1.AllItems) = CountRows(Filter(ECMmbrCQSmryGallery_1.AllItems, ComplianceLabel_1.Text = "Compliant" || ComplianceLabel_1.Text = "Certified" && CQTitle <>GalleryLabelName.Text ))

//Or For use below Code set the Toggle Visibility.

if(IsBlank(Set(ChecValue,Filter(ECMmbrCQSmryGallery_1.AllItems,CQTitle <>GalleryLabelName.Text ))),false,true)


Thanks,
Hope my solution resolves your Issue.

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."-Vijay




 

3csman
Impactful Individual
Impactful Individual

I made and edit to my formula and placed your suggestion on the end; same results. The Gallery does not show "Firefighter I" or "Firefighter II" as a result of the changes; consequently, the Toggle should show Inactive Red), but it doesn't.

 

CountRows(ECMmbrCQSmryGallery_1.AllItems) = CountRows(Filter(ECMmbrCQSmryGallery_1.AllItems, ComplianceLabel_1.Text = "Compliant" || ComplianceLabel_1.Text = "Certified" && CQTitle <> ECMmberCQTitleHtmlTxtFld_1.HtmlText ))

 

Screen.PNG

Hi @3csman :

I'm not sure if I understand your request, if there is an error, please help me point it out

When there is no record in ECMmbrCQSmryGallery_1 whose CQTitle value is "Firefighter I" or "Firefighter II", no matter what the result of 

 

CountRows(ECMmbrCQSmryGallery_1.AllItems) = CountRows(Filter(ECMmbrCQSmryGallery_1.AllItems, ComplianceLabel_1.Text = "Compliant" || ComplianceLabel_1.Text = "Certified"))

 

is, the value of the Toggle will be false.

If so,please try this code:

 

!IsBlank(LookUp(ECMmbrCQSmryGallery_1.AllItems,CQTitle="Firefighter I" || CQTitle="Firefighter II")) && 
 (CountRows(ECMmbrCQSmryGallery_1.AllItems) = CountRows(Filter(ECMmbrCQSmryGallery_1.AllItems, ComplianceLabel_1.Text = "Compliant" || ComplianceLabel_1.Text = "Certified")))

 

Best Regards,

Bof

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (3,661)