Hide in legend if Isnull.

[schuffe]

Hello.

The screenshot describes my problem pretty well.

If a field is null everywhere, how can i hide it in the legend?

Thanks 🙂

[stevedark]

Hi @schuffe 

Change the legend for column B to be:

=if(alt(max(B),0) = 0, null(), 'B')

If B has a string in it, rather than numbers use len(maxstring instead of alt(max(.

What are your expressions in the line chart? You should have an aggregation, even if you don't need to aggregate, and this may fix things anyway. Rather than just having B have avg(B) or max(B) - even though you know it will be a max or an average of one.

Hope that helps.

Steve

[schuffe]

Thanks for your answer 🙂

Now i get this legend. 

But I want only A to be shown in the legend. 

Is this possible?

[stevedark · Accepted solution]

Hi @schuffe 

Try making the expression for A sum({<A*={*}>}A) and for B sum({<B*={*}>}B) .

Another approach is setting the Show Condition on the measures to be:

=alt(max(B),0) > 0

That should work.

In some ways, loading the data so that there is a single value column and a type column would be better. That way you would have a line chart with dimensions of Year and Type and an expression of sum(Value) if there were no values of type B it wouldn't show.

This could be done by loading with a CROSSTABLE or loading twice.

The crosstable approach would be something like:

CROSSTABLE (Type, Value, 1) LOAD
   Year,
   A,
   B
FROM your data source here...

This will create a table with three columns, Year, Type and Value as described above. If you have more tables ahead of the A and B then replace the 1 with the number of columns before those you wish to cross table.

Getting the data structure right makes everything work better.

Cheers,
Steve

[schuffe]

Thank You very much.

Those Crosstables are perfect for my application.

Thanks for your work 🙂

[stevedark]

Hi @schuffe 

Thanks for confirming. Crosstables can be really useful for simplifying data models. Fewer columns and more rows are always better. You just need to be careful with them if loading multiple crosstables in a loop or using a preceding load. Often you need to use temporary tables and resident loads with them.

Thanks also for marking the answer as a solution - this is really helpful for other Community users.

Steve