Forum Discussion
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.
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.
14 Replies
- v-chuncz-msftCommunity Support
- spotpuffFrequent Visitor
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.
- DogResponsive 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
- FahadShamim690Regular Visitor
Format function returns empty instead of blank.
So try followingVar A= FORMAT ( AVERAGE ( 'Delivery'[ShelfTime] ), "HH:MM:SS" )
Return if (A & "--" == "--" , blank(), A )