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 mea...
Need Help
  • 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.

v-chuncz-msft's avatar
v-chuncz-msft
Community Support
9 years ago

spotpuff,

 

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

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

  • Dog's avatar
    Dog
    Responsive Resident
    9 years ago

    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

    • Dog's avatar
      Dog
      Responsive Resident
      9 years ago

      Sorry just noticed that yours isn't just percentages. 

       

      • spotpuff's avatar
        spotpuff
        Frequent Visitor
        9 years ago

        Dog No worries.

         

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

    • spotpuff's avatar
      spotpuff
      Frequent Visitor
      9 years ago

      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.