Forum Discussion

pratikrami's avatar
pratikrami
Regular Visitor
1 year ago
Solved

Don't want to display some fields on X-axis title but I need to add those on X-axis.

I have Clustered column chart. I want to display title of task of x-asix and overdue days on y-axis for each task. Now title might be same for multiple tasks. If I add only title field on x-axis, it ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi pratikrami ,
    Thanks for Ritaf1983 reply.

    According to your description, you want to show all rows of data on the bar chart, rather than wanting automatic aggregation based on Title, and as you say for adding the id column to the X-axis will cancel the aggregation, but you don't want to show the id column. In power bi we can't hide specific x-axis hierarchies, but we can avoid aggregation and avoid leakage of id information by giving unique serial numbers to different titles. The first way is to create index columns in power query and merge them using dax.

    1.Open Power Query and add a index column

    2.Create a column using dax

    Title_Index = CONCATENATE('Table'[Title] & "-",'Table'[Index])

    3.Final output

    The second way is to create a rank column using dax.

    X = 
VAR CurrentTitle = 'Table'[Title]
VAR CurrentOverdueDays = 'Table'[overdue days]
VAR CurrentId = 'Table'[Id]
VAR _Rank = 
    RANKX(
        FILTER(
            ALL('Table'),
            'Table'[Title] = CurrentTitle
        ),
        'Table'[overdue days] * 100000 + 'Table'[Id],
        ,
        ASC,
        DENSE
    )
RETURN
IF(
    _Rank = 1,
    'Table'[Title],
    CONCATENATE('Table'[Title] & "-", _Rank)
)

    Fianl output

     

    Best regards,
    Albert He

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

     

     

pratikrami's avatar
pratikrami
Regular Visitor
1 year ago

My requirment is to display overdue days for each title. but title can be same. 

I dont want to display Id as it is not user friendly and it is something we use in backend.

 

On tooltip also it doesnt look good as out title is something as below and if Id is appended to it, it seems part of title. cpnsider this protocol as title column and Id as Id column.

 

 

Ritaf1983's avatar
Ritaf1983
Super User
1 year ago

So, essentially, we're talking about ranking titles based on overdue days.
A horizontal bar chart is more appropriate because it's easier for us to compare the sizes of rectangles stacked one under the other.
Therefore, what I recommend is moving towards a combined table and graph.

 


If the ID doesn’t add any important information, just reduce it to a minimum.
+ If there's a known threshold for when the overdue days become critical, add a red circle to draw attention.

The pbix is attached

If my answer was helpful please give me a Kudos and accept as a Solution.