Forum Discussion

spotpuff's avatar
spotpuff
Frequent Visitor
9 years ago
Solved

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.

 

  • spotpuff's avatar
    spotpuff
    9 years ago

    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

    • spotpuff's avatar
      spotpuff
      Frequent 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.

      • Dog's avatar
        Dog
        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

  • Format function returns empty instead of blank.

    So try following

     

    Var A= FORMAT ( AVERAGE ( 'Delivery'[ShelfTime] ), "HH:MM:SS" )
    Return if (A & "--" == "--" , blank(), A )