Measure on Axis in grouped bar chart

[Hidrolix]

Hello all,

This is my first project, so it is still a steep learning curve for me. I checked the forum I saw some related items, but still could not get it to work. So I hope you guys and girls can help me a bit on how to proceed 😀

Final goal: Use the seperate (Direct Query - SQL database) contiguousUSpace value's in a clusterd Column chart.

Example data + PowerBI file: Test Measure in visual.pbix

My progress:

1. I try to get information from ContiguousUspace into seperate values by using the komma.

I used an measure for this 2UCount, 3UCount and 4Ucount. (in reality i also got 5Ucount and 6Ucount) (dax formula's are added in PowerBI file.
2. I used the seperate measures into the table (see left of picture below this) Everything looks good

3. Use these valeus's to show into clustered Column chart. -> Can only be used in tooltip.

So how to proceed from here ?

Side Information:
- Direct query connection to SQL
- Not allowed to use import mode

Data:
1. ContiguousUSpace - Direct Query from SQL database - Consists of several values split by commas. This refers to the space in a cabinet that is available.
- 2Ucount,3Ucount,4Ucount Measures created that pull apart the different values needed to display in the clustered column chart
- AssetID is the link to the cabinet no where this space is available.

2. Asset - Direct Query from SQL database - This contains the translation from AssetID to Cabinet/Rack nr.
-Asset ID - Unique numbering of assets and thus used as translation to other data
- Rack nr - Is the cabinet/rack nr.

Data model:

[Hidrolix]

Thanks for the response 🙂 Now I feel silly I didn't saw the formatting was wrong.

And indeed the 3U and 4U worked flawlessly!! For the 2UCount the formula did not work, but I adjusted it so it worked the same as 3U and 4U.

2U Count = VALUE(PATHITEM(SUBSTITUTE('ContiguousUSpace'[Calc ContiguousUspace],",", "|"), 1))

[v-junyant-msft · Accepted solution]

Hi @Hidrolix ,

The reason that the measure you create can only go to the legend and not to the y-axis is that all measures return a text type result and not a number. Please add "VALUE( " at the beginning of all measures and " )" at the end.

2U Count = 
VAR __text = SELECTEDVALUE( 'ContiguousUSpace'[ContiguousUSpace] )
VAR A =
LEFT( 
        __text, 
        SEARCH( ",", __text, 1, 1 ) -1 
    )
RETURN 
    VALUE(A)

3U Count = 
VALUE(PATHITEM(SUBSTITUTE('ContiguousUSpace'[Calc ContiguousUspace],",", "|"), 2))

4U Count = 
VALUE(PATHITEM(SUBSTITUTE('ContiguousUSpace'[Calc ContiguousUspace],",", "|"), 3))

The final output is below:

Please adjust the number of decimal places.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.