default data colours in a visualisation with NaN value

[Anonymous]

Data colours can be linked to a value.

The values can become NaN under certain conditions.

The default data colour when NaN currently maps directly on to the existing user value range (min, centre, max).

NaN is however out of scope of the range.

It would be very useful if the user could also provide a colour and/or some other settings for NaN.

As it is, the user now sees (on my visual) the maximum value colour for NaN. This is not correct and veyr misleading.

Complex work arounds may exist, but there are diminishing options given the current way in which computed columns, measures in context and visualisation control parameters are set up.

[v-piga-msft]

Hi @Anonymous,

I'm not sure if the feature Color formatting by field value will meet your requirement, you could have a try with it.

Best Regards,

Cherry

[Anonymous]

Thanks @v-piga-msftCherry. I just checked this out.  I was hopeful but

- with a table

                 - there is no option to indicate "NaN", only "blank"

- with a Scatter plot

                 -there is no equivalent "conditional format" design element 

                 - the nearest design element is "Data Colors"

                 - I cannot use some sort of compromise "Legend" as in my case these also cannot use a measure as the value

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[Anonymous]

Hi Cherry.

I'd like to close it but I can't ... the proposed solution does not fully cover the case I was dealing with. I'm using a scatter plot, the proposal dealt with a table visualisation. Scatter plots do not have advanced conditional colour logic for blanks. 

OK to be fair, the original post did not anticipate all combinations of visualisations and all the peculiar mathematical outcomes and also did not specify scatter plot. 

To make more progress, a test data table Table1 is given below:

row      A          B          CC_A divided by B        CC_ISNUMBER CC_ISBLANK     CC_ISERROR

1          1          1          1          True     FALSE   FALSE

2          0          1          0          True     FALSE   FALSE

3          1          0          Infinity  True     FALSE   TRUE

4          1                     Infinity  True     FALSE   TRUE

5                     0                     False     TRUE    FALSE

6          0          0          NaN     True     FALSE   TRUE

I would say it would be best if both PBI and DAX are further developed thus:

A) ensure that ALL visualisations can address EACH of the distinct outcomes given in col <CC_A divided by B> in rows {3,4,5,6} in the SAME conditional colour logic

AND/OR

B) a new DAX function is provided to cover each of the distinct outcomes given in col <CC_A divided by B> in rows {3,4,5,6}

make sense ?

[Anonymous]

Table1   synthetic test data set

[Anonymous]

It's an aside, but maybe there is some interest in :

row4> 1/BLANK == Infinity.   This implies BLANK is treated as a zero

row5> BLANK/0 == BLANK.   Should the result be NaN to be consistent with row4 and row6 ? 

[v-piga-msft]

Hi @Anonymous,

What about creating a calculated column to replace the NAN value with the blank value?

Then you could use the color formatting with table visual. This maybe a workaround.

Best  Regards,

Cherry

[Greg_Deckler]

You should post this to the Ideas forum.

[Anonymous]

OK thanks. I thought this probably was the best forum (Desktop/Help?) but will also try to find "Ideas".

That was help in itself of course 🙂

As an aside, there seem to be multiple ways these days of submitting what I would call :

- problem reports  (PR)

- change requests  (CR)

- help requests       (HR)

and now seemingly multiple registration processes across MS, PowerBI and GitHub. Bit confusing and surely leads to duplications all over the place.

It is course another matter entirely but I hope it can all be brought together at some stage soon.