Attempting to use a Measure to display Month (stored as Integer) as text value

[apagan]

I am retreiving data from a table that stores a month as an integer (i.e., January = 1, February = 2, etc.) and I want to display that number as a text value in a graph.  I've attempted to use a measure to create a text value for the month, but am getting the error message, "A single value for column 'SampledMonth' in table 'LocationVisits' cannot be determined.'

Here is the formula that i am using:

Month = if(LocationVisits[SampledMonth]=4,"April","")

What am i doing wrong?

Thanks!

[v-micsh-msft · Accepted solution]

Hi apagan,

As shared, a measure is not recommended to put into X-Axis.

In addition, for the month, if all the months value that show in your table are in a same year, then we could create a reference table, which matchs month number with month name;

For example:

Month Num |  Month Name

                 1  |   Jan

                 2  |   Fer

Input all the month values, then create a relationship under Relationship View in Power BI Desktop. After that, put the Month Name as the X-Axis in the visual.

Another way is create a calculated column with switch function:

Month Name = SWITCH(LocationVisits[SampledMonth], 1, "January", 2, "February", 3, "March", 4, "April" 
               , 5, "May", 6, "June", 7, "July", 8, "August" 
               , 9, "September", 10, "October", 11, "November", 12, "December" 
               , "Unknown month number" )

And if your data table contains month in different years, we need the year information also included in the column (we could use format function here), or in the calculation filters (to filter with the same year), well this depends on your data sample.

Please reply back if you need any further assistance on this.

Regards

[vickyprudhvi]

HI,

are you palnning to show months on x- axis?

you need to create calcualted column and not measure for it. As per my knowledge measures can't be used in Axis. Measures are used in Values section of the visual

[apagan]

Thank you!