topic Re: Text available in Search/Filter but no value in App Development

Text available in Search/Filter but no value

Daniel1908 — Fri, 16 Apr 2021 16:07:10 GMT

Hello together,

I have created a worksheet with different fields.

The formula in the background works well, but in the search field are Numbers/Text available which are not present in the table.

When selecting the fields I then get the value 0 displayed.

Any experience on that?

Kind Regards,
Daniel

Re: Text available in Search/Filter but no value

Anil_Babu_Samineni — Fri, 16 Apr 2021 17:29:21 GMT

Hello, Can you please explain in details?

Re: Text available in Search/Filter but no value

Daniel1908 — Mon, 19 Apr 2021 07:29:39 GMT

Hi,

in my table I got for example 100 Material Numbers.
But in the Search Field appear quite more and if I select them I then get the value 0 displayed.

I want to get these out of the search field.
It should only show the 100 which are in my table.

Kind Regards,
Daniel

Re: Text available in Search/Filter but no value

marcus_sommer — Mon, 19 Apr 2021 08:35:44 GMT

It sounds that your fact-table with the transactions contained not all materials which are listed within the linked dimension-table. It's not uncommon because the facts are usually limited to last n years but the dimensions contain all ever existing items. To overcome it you could filter the dimension-loads on the existing items (requires that they are loaded afterwards of the fact-tables), maybe in this way:

fact: load * from transactions;
dim: load * from dimension where exists([Material Number]);

- Marcus

Re: Text available in Search/Filter but no value

Daniel1908 — Mon, 19 Apr 2021 08:56:34 GMT

Is there a way to get this solved in the formula itself?

Count({<ProductKey*={"TH"}>*<[Returnlabel.]-={"A"}>*<[Price1]-={"0,00"}>*<[Price2]={"0,00"}>}distinct Material)

Kind Regards
Daniel

Re: Text available in Search/Filter but no value

marcus_sommer — Mon, 19 Apr 2021 09:09:41 GMT

No, not really. You could easily exclude them with set analysis within the expressions but the dimension-values exists further within the field and therefore within the search. You may create a calculated dimension within the list-box or the charts but such an approach has several disadvantages by aggr(), totals, displaying selections ... and is therefore rather not a general solution.

Therefore I suggest to do it within the script - it's simple and quite probably there are more dimensions which may be filtered in a similar way, like: stores, staff, regions/channels ....

- Marcus