Forum Discussion

rds_2019's avatar
rds_2019
Helper I
6 years ago
Solved

Can we have different reference value and show value

hi All,

Can we have different value in Bar chart from visual purpose and reference purpose.

So what i want is on x axis. Value should be sorted based on the Date (24th Sept, 25 Sept,25 Sept,25 Sept, 26 Sept etc)

but where as when it shws on the bar it should show like 

 

24th Sept

25th Sept_Test1

25th Sept_Test2

25th Sept_Test3

26th Sept

 

Is it possible

 

Cheers,

RDS

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi rds_2019 ,

    Try this:

    IsDistinct = 
IF (
    CALCULATE ( COUNT ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) ) = 1,
    1,
    0
)
    Date_Test = 
VAR Rank_of_not_IsDistinct =
    RANKX (
        FILTER ( 'Table', 'Table'[IsDistinct] = 0 ),
        'Table'[Date] + 'Table'[Value] / 10000,
        ,
        ASC,
        DENSE
    )
VAR Test =
    IF (
        'Table'[IsDistinct] = 0,
        CONCATENATE ( "Test", Rank_of_not_IsDistinct ),
        BLANK ()
    )
VAR Day =
    IF (
        DAY ( 'Table'[Date] ) = 1
            || DAY ( 'Table'[Date] ) = 1
            || DAY ( 'Table'[Date] ) = 21
            || DAY ( 'Table'[Date] ) = 31,
        CONCATENATE ( DAY ( 'Table'[Date] ), "st" ),
        IF (
            DAY ( 'Table'[Date] ) = 2
                || DAY ( 'Table'[Date] ) = 22,
            CONCATENATE ( DAY ( 'Table'[Date] ), "nd" ),
            IF (
                DAY ( 'Table'[Date] ) = 3
                    || DAY ( 'Table'[Date] ) = 23,
                CONCATENATE ( DAY ( 'Table'[Date] ), "rd" ),
                CONCATENATE ( DAY ( 'Table'[Date] ), "th" )
            )
        )
    )
VAR Month_Name =
    LEFT ( FORMAT ( 'Table'[Date], "mmmm" ), 4 )
VAR Date_Test_1 =
    CONCATENATE ( CONCATENATE ( Day, " " ), Month_Name )
VAR Date_Test_2 =
    CONCATENATE (
        CONCATENATE ( CONCATENATE ( CONCATENATE ( Day, " " ), Month_Name ), "_" ),
        Test
    )
VAR Date_Test =
    IF ( 'Table'[IsDistinct] = 1, Date_Test_1, Date_Test_2 )
RETURN
    Date_Test

     

    PBIX file attached.

     

    Best Regards,
    Icey

     

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

3 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    If you have a column with the dates, and a column with the values "test 1", "Test 2" etc, then you could create a calculated column that combined the date and the label and gave you what you want to display on the bars.

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi rds_2019 ,

    Try this:

    IsDistinct = 
IF (
    CALCULATE ( COUNT ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) ) = 1,
    1,
    0
)
    Date_Test = 
VAR Rank_of_not_IsDistinct =
    RANKX (
        FILTER ( 'Table', 'Table'[IsDistinct] = 0 ),
        'Table'[Date] + 'Table'[Value] / 10000,
        ,
        ASC,
        DENSE
    )
VAR Test =
    IF (
        'Table'[IsDistinct] = 0,
        CONCATENATE ( "Test", Rank_of_not_IsDistinct ),
        BLANK ()
    )
VAR Day =
    IF (
        DAY ( 'Table'[Date] ) = 1
            || DAY ( 'Table'[Date] ) = 1
            || DAY ( 'Table'[Date] ) = 21
            || DAY ( 'Table'[Date] ) = 31,
        CONCATENATE ( DAY ( 'Table'[Date] ), "st" ),
        IF (
            DAY ( 'Table'[Date] ) = 2
                || DAY ( 'Table'[Date] ) = 22,
            CONCATENATE ( DAY ( 'Table'[Date] ), "nd" ),
            IF (
                DAY ( 'Table'[Date] ) = 3
                    || DAY ( 'Table'[Date] ) = 23,
                CONCATENATE ( DAY ( 'Table'[Date] ), "rd" ),
                CONCATENATE ( DAY ( 'Table'[Date] ), "th" )
            )
        )
    )
VAR Month_Name =
    LEFT ( FORMAT ( 'Table'[Date], "mmmm" ), 4 )
VAR Date_Test_1 =
    CONCATENATE ( CONCATENATE ( Day, " " ), Month_Name )
VAR Date_Test_2 =
    CONCATENATE (
        CONCATENATE ( CONCATENATE ( CONCATENATE ( Day, " " ), Month_Name ), "_" ),
        Test
    )
VAR Date_Test =
    IF ( 'Table'[IsDistinct] = 1, Date_Test_1, Date_Test_2 )
RETURN
    Date_Test

     

    PBIX file attached.

     

    Best Regards,
    Icey

     

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

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi rds_2019 ,

     

    Is this problem solved?


    If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.


    If not, please let me know.


    Best Regards
    Icey