Contextualization of a filter parameters

[JeanFred]

Hi all

I have been posting but it remain unsolved (I will link to this answer if anyone can find the way to proceed)

In a page I am struggling with creating a contextualization in the formulas of the entire page

My overall attempt : contextualize depending on the business name that appear on an item somewhere in the page 

Seems simple ? yes but so far I never found the right approach

The initial formula (that is working well 🙂 )

Text(
Sum(
Filter(
'PROJECTDB';
BUSINESS1>= 1);
(BHWQ1+BSWQ1+BINQ1+BEXQ1+BHWQ2+BSWQ2+BINQ2+BEXQ2+BHWQ3+BSWQ3+BINQ3+BEXQ3+BHWQ4+BSWQ4+BINQ4+BEXQ4)*BUSINESS1/100)
/1000;"[$-en-us]#,##")

So: I am creating an Item called BUSINESSNAME and for this page the value is BUSINESS1 and I am trying this formula 

Text(
Sum(
Filter(
'PROJECTDB';
Value(BUSINESSNAME.Text) >= 1);
(BHWQ1+BSWQ1+BINQ1+BEXQ1+BHWQ2+BSWQ2+BINQ2+BEXQ2+BHWQ3+BSWQ3+BINQ3+BEXQ3+BHWQ4+BSWQ4+BINQ4+BEXQ4)*Value(BUSINESSNAME.Text)/100)
/1000;"[$-en-us]#,##")

or this formula :

Text(
Sum(
Filter(
'PROJECTDB';
BUSINESSNAME.Text >= 1);
(BHWQ1+BSWQ1+BINQ1+BEXQ1+BHWQ2+BSWQ2+BINQ2+BEXQ2+BHWQ3+BSWQ3+BINQ3+BEXQ3+BHWQ4+BSWQ4+BINQ4+BEXQ4)*ValueBUSINESSNAME.Text/100)
/1000;"[$-en-us]#,##")

always the same results : no results (empty Item) Instead of properly proceeding the calculation - I know that the 1st way to proceed should work ! but it does not work (probably due to the other elements of the formula)

Any Idea? I am REALLY Struggling with it ! 😞

Many thanks

BR

Jean fred

[BCLS776]

If I understand you correctly, the BUSINESSNAME.Text property in some cases contains a string such as: "BUSINESS1"? If that's the case, the Value() function will not evaluate a string such as "BUSINESS1". Value() can only operate on a string that looks like a number, e.g. "12.34".

Instead, try using an If() or Switch() to control the logic of your app:

Switch(BUSINESSNAME.Text,
    "BUSINESS1", /* Insert code for case 1 here */ ,
    "BUSINESS2", /* Insert code for case 2 here */ ,
    // and so on
    /* Insert code for default case as the last argument */
)

Hope this helps,

Bryan

[JeanFred]

Thank you for the proposal @BCLS776 

Now I understand why the value() function does not work

Yes the idea is to have the text of business 

My intend is more to contextualize the page ... and in my code to have businessname as a variable

in your solution I do have to repeat the code plenty time ...for business 1 ...etc and as I do have a lot of figures to retrieve in the page not the simplest

Is there any other way to manage to have a variable ? I can also change the way I do build the page if needed 

[BCLS776]

Yes, you can use the Set() function for global variables, UpdateContext() for screen-scope variables, or even use a collection to temporarily store some values.

Perhaps the solution to your issue is to use a Switch() to set a variable to the value you need for a subsequent calculation?

[JeanFred]

HI 

thank you @BCLS776 

So I have been trying the switch function as initially proposed and it lead to at least 8 pages of code / data (to heavy too long to code ... not what I need) 

SO I have been Trying your proposal of SET() (or actually I have been trying UpdateContext() as the Idea is to change depending on each page (one page for business1 , one for Business2...)

The update context is working well 

when i Try to integrate the variabe in the code the retunr is the same : blank field

I have been using this formula : maybe i did it wrong ? :

First I did a button (to update context)

UpdateContext({BusinessName:"Business1"})

Text(
Sum(
Filter(
'2022 DB';
BusinessName>= 1);
(BHWQ1+BSWQ1+BINQ1+BEXQ1+BHWQ2+BSWQ2+BINQ2+BEXQ2+BHWQ3+BSWQ3+BINQ3+BEXQ3+BHWQ4+BSWQ4+BINQ4+BEXQ4)*BusinessName/100)
/1000;"[$-en-us]#,##")

any Idea? I did smthg wrong?

many thx

BR

JeanFred

[BCLS776]

Hi @JeanFred,

Yes, there is an issue with your code. The statement: UpdateContext({BusinessName:"Business1"}) sets the value of the variable BusinessName to a string that contains "Business1". Then your subsequent filter and calculation attempt to compare this string to a numerical value and perform a mathematical operation on it:

Text(
Sum(
Filter(
'2022 DB';
BusinessName>= 1);
(BHWQ1+BSWQ1+BINQ1+BEXQ1+BHWQ2+BSWQ2+BINQ2+BEXQ2+BHWQ3+BSWQ3+BINQ3+BEXQ3+BHWQ4+BSWQ4+BINQ4+BEXQ4)*BusinessName/100)
/1000;"[$-en-us]#,##")

I have marked in bold text where BusinessName is being handled as though it has a numerical value. In this Filter() you are asking Power Apps to evaluate "Business1" >= 1, which doesn't work so the Filter() returns no records.

Is BusinessName supposed to be a numerical value in your app, or do you need to substitute something else there whenever BusinessName equals a particular string (e.g. "Business1")?

Consider this example code:

If( // must go in an behavior field, such as OnSelect
    Label1.Text = "Business1", UpdateContext({ varBusiness: 0.5}),
    Label1.Text = "Business2", UpdateContext({ varBusiness: 1.5}),
    Label1.Text = "Business3", UpdateContext({ varBusiness: 2.0}),
    UpdateContext({ varBusiness: 3.0})
)
// This statement sets varBusiness to different values depending on the contents of Label1.Text

varBusiness in this example always gets a numerical value, so it can be compared to other numerical values or used in a mathematical expression. Is that more like what you are trying to do?

Bryan

[JeanFred]

Hi Bryan ! (@bcls776) 

First of all I d like to warmly thank you for the time you dediate to support me and this community - Very much appreciated

Actualy in my code, Business1 is the name of the column (as is business2 business 3...)

Each column mays have numerical values in it to split the spend between the different business (egs: Business1=20% Business2= 50% and Business3=30%) 

my attempt is

1-to create a page for each business 

2-to check in each column if is there any % indicated for this specific business

3-If yes then to sum columns and then to proratize depending on the value indicated in the column business

So in my example I would like to sum all the lines of the columns BHWQ1+BSWQ1+BINQ1+BEXQ1+BHWQ2+... if the column business1 >1% and then to divide it by the percent of business1 of each line

The thing is that it is working perfectly when I harcode Business1 instead of making it variable (I can hardcode it but it will take me ages to change the value of business1,2 etc in each pages

is it more clear ?  🙂 (sorry not that easy to explain)

[BCLS776]

---

@JeanFred wrote:

Actualy in my code, Business1 is the name of the column (as is business2 business 3...)

Each column mays have numerical values in it to split the spend between the different business (egs: Business1=20% Business2= 50% and Business3=30%) 

JeanFred, this changes things quite a bit - Power Apps does not support dynamically switching columns in an expression. In fact, your underlying data structure might be leading you to do things that will be difficult for you later on. It sounds like you are trying to store ALL your data in a single table, which leads to a massive, difficult-to-manage table later on. Strongly consider if you should be organizing your data into at least two tables: one with your main database, and a second that is used for looking up these percentages. The second table would have a column structure like this:

Table2

BusinessName

PortionName

PortionValue

And the rows would look something like:

BusinessName	PortionName	PortionValue
ABC Company	Business1	0.20
ABC Company	Business2	0.50
ABC Company	Business3	0.30
XYZ Company	Business1	0.50
XYZ Company	Business2	0.50

With this table structure, you can use the LookUp() function to put out specific values to use in your calculation. For example, LookUp(Table2, BusinessName = "ABC Company" && PortionName = "Business2", PortionValue) returns a value of 0.5 that you can use in a calculation. You can substitute other values in your app for the search strings (e.g. Label1.Text for "ABC Company") to avoid hardcoding many cases. This is how you handle dynamic values in an app in a way that you can maintain/sustain in the future. Make sense?

[JeanFred · Accepted solution]

o and Thank you @BCLS776 

I will review my DB : despite the advantage of having an easiest way to search for data is there any other avantages to have various small tables ? will it make the apps more rapid ? 

For my specific request For Any reason I discovered that when I am duplicating the screen business1 and rename the new scrren Business2 Power apps automatically adapt the content of each line of code with Business2 !!!! thas a Very good news answering my need ! 😉 

many thanks again

BR

Jean fred

[BCLS776]

---

@JeanFred wrote:

I will review my DB : despite the advantage of having an easiest way to search for data is there any other avantages to have various small tables ? will it make the apps more rapid ? 

---

Smaller tables are naturally faster to process than very large tables. They are less prone to data limit problems, and they make it easier to add functionality to & maintain your app in the future. Finally, once you start structuring your data across multiple tables, you should see how intuitive the data structure becomes.