Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
spotpuff
Frequent Visitor

Format causing measure to return blank.

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.

 

1 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.

View solution in original post

13 REPLIES 13
v-chuncz-msft
Community Support
Community Support

@spotpuff,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Responsive Resident
Responsive Resident

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
Frequent Visitor

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
Responsive Resident
Responsive Resident

Sorry just noticed that yours isn't just percentages. 

 

spotpuff
Frequent Visitor

@Dog No worries.

 

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

Dog
Responsive Resident
Responsive Resident

@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
Frequent Visitor

@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
Responsive Resident
Responsive Resident

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
Frequent Visitor

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.

@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

@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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors