Non numeric measure not available in a powerview ?

[Lolo]

Hello,

In DAX (in Excel power pivot) you can create non numeric measures and display it in a pivot table.

For example a dynamic measure called STATUS, that return « SURPLUS » if the sum of values is POSITIVE and return « DEFICIT » if the sum of value is NEGATIVE.
I can easily add it in the pivot table and see the result, split by positive/negative.

But what if I want to use this text measure in power view ?
- in a table
- or as a color category in a map ? To be able to colour differently bubbles in SURPLUS or in DEFICIT.

The measure is not available in the table in the powerview sheet


Did you have already this kind of need ? How could I tackle it?

Thank you for your help.

 

[Chihiro]

1. Instead of using measure you'd use matrix visual with hierarchy and filtering to return desired result set in most cases. However, with right hierarchy set up, you can use non numeric measure in a matrix (I wouldn't do it in a table).

Have a read of link below.
http://www.sqlservercentral.com/articles/DAX/147787/

2. Just use underlying numeric logic for formatting. I.E. >0 blue, <0 red etc.

 

[Lolo]

I will check for matrix visual in power view, ok.
But I guess you are talking about power bi, not. Excel/power view...
Indeed the measure is NOT AVAILABLE in the field/measure list in the power view sheet.
So not sure I will be able to use it even on a matrix visual...

For the link, already seen it indeed.
Very interesting, but it deals with power bi too, not excel. Interesting for Dax part however, but visual possibilities in power bi are better than on excel/powerview.

In power view you cannot set map bubble color based on value, or I have missed something.
You have to provide a field that make the split.
Or you have to create a measure for negztive values and another for positive values, but in power view you cannot set 2 measures within the same map so obliged to create a map for negative values and another one for positive values, but this is not what I want

My concern was only about powerview in excel, but perhaps i was not clear.

 

[Chihiro]

Maps in PowerView or PowerBI has quite a bit of limitations.
I've used custom map in the past, but it was quite a bit of work, and I avoid it as much as I can.

In my opinion, in most cases there are better alternatives to maps for data visualization.

As for Matrix I'll play with it tomorrow and see.

FYI - The link is for SSAS tabular and not PowerBI, though Excel Power suites and PowerBI also share many of same features and share same vertipac engine

 

[Chihiro]

Ah, you are right, non-numeric measure isn't supported in PowerView. There is ticket in with MS Feedback. This is for SSAS PowerView but Excel PowerView uses same engine.

I've submitted user voice for Excel Power View. Link below (still may be pending processing).
https://excel.uservoice.com/forums/...add-non-numeric-measure-support-to-power-view

However, you can still use it in PowerPivot. Instead of in Power View for now.

 

[Chihiro]

As for PowerMap color format. You can do this by using multiple layers.

1. Add measure for Positive Value & Negative Value to model.
2. To layer 1 add location and Positive. Set color.
3. To layer 2 add same location field and Negative. Set Color.

 

[Lolo]

Thank you for your checks. Vote +1
Good idea about positive and negative layers in 3D Maps.