Unintended filtering

[sskinner]

Hi, hoping you can help with this one!  The data model has two tables, Projects (master, unique Number) and Metrics (which are point-in-time records with a 0:n association to the Projects table (there may be no records, there may be several). A single project instance of Metrics looks like this…

My desired output is a table list of all projects (filterable) with a column of the latest MetricCondition (MetricCurrent=’True’, of a specific MetricName (StatusMessageAge in this example) when it exists, ‘NA’ when it does not. I am not sure if this is the best way, but I can achieve the display with this column expression:

Only({$<MetricName={'StatusMessageAge'},MetricCurrent={'True'}>}MetricCondition)

The problem is that the table only displays records when the association exists (when there is a qualified Metrics record). I need to display all the Projects. I have tried a few things (if(….), alt(….), MxString instead of Only,...) but cannot get any to work (I may not be structuring the statements correctly).

Open to any ideas. Thanks!

[jberna26]

Hi @sskinner 

Not completely sure I understood.

So you are applying that measure, with the dimension Project in a table?

But since you are applying filters (and "measuring") the MetricCondition, if it doesn't exist for one project, that project will not show up.

You could try and go to add-ons of the table and show the zero values, but since you want to show 'NA', it will not solve your problems.

You said you tried an if statement, can you give an example?

I would try something like:

If(Count(MetricCondition)=0, 'NA',Only({$<MetricName={'StatusMessageAge'},MetricCurrent={'True'}>}MetricCondition))

Hope it helps 😉

Regards

[sskinner]

Hi, yes - your understanding is correct.  Here is the kinds of thing I was trying (to evaluate the return of the set-analysis);
if(Only({$<MetricName={'StatusMessageAge'},MetricCurrent={'True'}>}MetricCondition)<>Null(),MetricCondition,'NA')

Your statement, however, seems to work! (I will do some more testing). Can you explain the logic for me though?  When count(MetricCondition) is evaluated, is that applied to the data after it has gone through the set analysis part of the statement?  For example, will it still work if there are "Metric" records with that project number but none that meet the conditions of MetricName={'StatusMessageAge'},MetricCurrent={'True'}? (I will try that)

Thank-you very much!

[jberna26]

Hi @sskinner ,

no, sorry, it was an oversight on my part. 
You should use the same set analysis in the count, so that it works properly. 

Just to explain my line of thought, you would have to assign a value to the nulls in the measure ( in this case NA), otherwise it wouldn’t work. Alt here wouldn’t work as well, as the result is not in number format. 

An if statement is non deterministic in a front end object, and the set analysis is calculated once per object, but evaluated for every row (or is it the other way around? I’m never sure 🤔)

Hope that I cleared it up a little more 😉

Cheers

[jberna26]

By the way, if you are happy with the solution, mark the topic as solved 🙂