Format causing measure to return blank.

[spotpuff]

I'm using a measure with disconnected slicers to return a value depending on how the user interacts with the report. They can choose to view values as values or percentages, and can choose from 3 measures.

I have 6 measures to handle the different cases, and a final measure that handles the final display based on the switch. Mathematically everything is working fine, however when I try to format the percentage display with the FORMAT() function, I only get blanks returned.

Here's an example code of one of the base measures:

Histogram Count = 
VAR BucketFloor =
    MIN ( DimHistogramBuckets[Floor] )
VAR BucketCeiling =
    MIN ( DimHistogramBuckets[Ceiling] )
RETURN
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                VALUES ( Dim[Group] ),
                "Pct Change From Previous Year", [Percent Change From Previous Year Rate]
            ),
            SWITCH (
                TRUE (),
                [Pct Change From Previous Year] = 0, [Pct Change From Previous Year] = BucketFloor
                    && [Pct Change From Previous Year] = BucketCeiling,
                [Pct Change From Previous Year] > 0, [Pct Change From Previous Year] > BucketFloor
                    && [Pct Change From Previous Year] <= BucketCeiling,
                [Pct Change From Previous Year] < 0, [Pct Change From Previous Year] >= BucketFloor
                    && [Pct Change From Previous Year] < BucketCeiling
            )
        )
    )

Here is the final measure that controls the displayed measure on the report:

Histogram Selected Measure = 
//This measure returns a measure to be displayed in the histogram depending on switch options in the report.
VAR DisplayID =
    MIN ( DimHistogramDisplaySelect[ID] )
VAR MeasureID =
    MIN ( DimHistogramMeasureSelect[ID] )
RETURN
    SWITCH (
        DisplayID,
        1, SWITCH (
            MeasureID,
            1, [Histogram Count],
            2, [Histogram IE],
            3, [Histogram Premium]
        ),
        2, SWITCH (
            MeasureID,
            1, [Histogram Count Percent],
            2, [Histogram IE Percent],
            3, [Histogram Premium Percent]
        )
    )

In the code just above, in the case where DisplayID = 2, this is working just fine, but if I try to alter the code like so:

MeasureID,
            1, FORMAT ( [Histogram Count Percent], "0.0%" ),
            2, FORMAT ( [Histogram IE Percent], "0.0%" ),
            3, FORMAT ( [Histogram Premium Percent], "0.0%" )

The measure now returns blanks instead of the appropriate values.

I have used this method before, so I cannot for the life of me figure out why this is happening. If anyone could be assistance, help would be greatly appreciated.

[v-chuncz-msft]

@spotpuff,

It's not very easy to reproduce this issue. So could you please share us the .pbix file?

[spotpuff · Accepted solution]

I can't share the file but I will work on building a comparable file that has the same data model layout and see if I can reproduce the issue.

I just tested the histogram bucketing and formatted measure in a table (rather than just a bar chart with labels) and it works fine, so now I don't know why it's not working properly with the bar chart. I believe the issue is that FORMAT() returns a text value, which the bar chart doesn't know is actually textual representations of numbers.

[Dog]

Hi @spotpuff

I've used something similar to this before and had a similar problem. the issue for me was that the "FORMAT" always returns a string in the given format and as most graphical visuals expect a number format it cannot display it. 

mine was slightly different in that my return values weren't all percentages so I had to set mine to a decimal number and just multiple or divide to get the correct output. Yours all appear to be percentages that are returned so I would have thought you'd be able to format the measure as a percentage (or custom with the necessary format) and remove the format function from the DAX return statement and leave as Count Percentage, IE Percentage and Premium Percentage. 

I hope this helps. 

Dog

[spotpuff]

Hi @Dog,

My values are both general number and percentages; you'll notice there are two SWITCH() functions in the "selected measure" statement.

Right now I'm going to go with a histogram by value and a histogram by percentage, but this is an unfortunate limitation of DAX/Excel functions.

I believe in Excel I solved this with VBA when the chart changes, but in Power BI it looks like two separate charts is how it will have to be.

[Dog]

Sorry just noticed that yours isn't just percentages. 

[spotpuff]

@Dog No worries.

On a side note, this is an unbelievably difficult topic to search for help on.

[Dog]

@spotpuff

you're not kidding, I went round in circles on this one. 

the only thing I had considered although never followed it through was to have a stacked bar graph that has two outputs one for percentage values and one for the numerical values. 

depending on slicer selection would depend on which one had data in to show on the graph - I'm assuming that it would then allow you to format the measures independently. 

If I give it a go I'll let you know. 

Dog

[spotpuff]

@Dog That's an interesting idea; let me know if it works. I wonder if the "auto" scaling for the chart is "smart" enough to switch between Percent and General (with scaling) if you do this!

[Dog]

Hi @spotpuff

it does and doesn't 🙂 

for me it will actual work fine as my client prefers data labels to the axis.

Data labels update accordingly and the correct format is shown for currency or percentage. 

The X Axis though is not clever enough as it displays, I assume, based on the first measure passed through in the values list which in my test case is currency (regardless if there are any values or not) 

Sorry

Dog

[spotpuff]

Hi @Dog I tried the solution you mentioned of stacked bar chart with one measure that returns a value, and one a percent (multiplied by 0 if it isn't to be displayed).  The chart works fine if the individual measures are formatted properly except for the Y-axis, which shows in percent. So I had numbers like 6,400bn% in the y-axis.

It's unfortunate that there isn't a way to dynamically format the y-axes as leaving these as "Auto" is best.

Even bar charts in the new experimental table visual don't work if you use Format(), which is, again, unfortunate.

[bremnnat]

@spotpuff

you sure aren't kidding when you say that it is near impossible to search for solutions for this. Just came across this post after a good hour of searching. This is also one of the first posts that I have seen someone else besides myself using the SWITCH( ) statement to dynamically swap filters based off a slicer. I was just recently attempting to do the same with columns and dynamically switching them in for the Row fields with a slicer in the same way...that was impossible to find anything on, but still got it.

I just ran into this same problem. It's really not a huge deal I just do not want my users to miss the fact that the data is actually percentages. Were you able to find any sort of work around? I cannot believe it is this difficult to accomplish.

Best,

Nathan

[spotpuff]

@bremnnat

Unfortunately I didn't find any solution. As of now I have 2 histograms, one for % and one for values.  It is interesting because they do vary, so maybe it's not the worst thing.

The stacked bar chart method does work; do a stacked bar chart with 2 measures, one a % and one a value.  When you use the slicer to select % or value, only the correct bar chart shows up (and the other zeroes out).  The only issue there is the y-axis is either in values or %, which is unfortunate. The only way I can think of getting around that is with a custom tooltip logic, maybe.

[bremnnat]

@spotpuff

I found somewhere to vote on this idea if you have not already https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15231165-conditional-formatted-me...