Not able to select the measure in the axis selection of charts.

[Anonymous]

Hi,

I created a measure but not able to select the measure in the axis selection, i tried using calculated column with the same calculations. But the column does not display any value.

In the below screenshot, measure displays value in the table, if i can use this measure in the axis that would be great,

if calculated column is the oly option left, please help me analayse as to why this column is not displaying values.

column = SWITCH (
SELECTEDVALUE ( 'Slicer Table'[Level] ),
"D", convert(FORMAT ( MAX ( 'Table'[Production Date-Time] ), "YYYY-MM-DD" ),STRING),
"E", convert(FORMAT ( MAX ( 'Table'[Production Date-Time] ), "YYYY-MM-DD HH:MM:SS" ),STRING),
"H", convert(HOUR ( MAX ( 'Table'[Production Date-Time] ) ),STRING),
"M", MINX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Year&Month] IN FILTERS ( 'Table'[Year&Month] )
),
convert(FORMAT ( 'Table'[Production Date-Time], "MMM DD" ),STRING)
)
)

[v-lid-msft]

Hi @Anonymous ,

We can pre-generate a axis table by using calculated table to meet your requirement:

Axis Table =
ADDCOLUMNS (
    CROSSJOIN (
        DISTINCT ( 'Slicer Table'[Level] ),
        DISTINCT ( 'Table'[Production Date-Time] )
    ),
    "Axis", SWITCH (
        [Level],
        "D", CONVERT ( FORMAT ( [Production Date-Time], "YYYY-MM-DD" ), STRING ),
        "E", CONVERT ( FORMAT ( [Production Date-Time], "YYYY-MM-DD HH:MM:SS" ), STRING ),
        "H", CONVERT ( HOUR ( [Production Date-Time] ), STRING ),
        "M", CONVERT ( FORMAT ( [Production Date-Time], "MMM DD" ), STRING )
    )
)

By the way, PBIX file as attached.

Best regards,

[Anonymous]

Thank you,  I need to avoid the level slicer instead I need to use a date slicer using production date time filed and based on the selection of dates in the date slicer, i need to claculate a time level measure that tells me as "M","D","H" or "E" and then based on this time level measure i should be able to create a axis table. I am attaching the pbix file below and the measures used below.

Time level = if([test date diff]>=90,"M",(

if([test date diff]>=4,"D",(

if ([test date diff]>=1,"H","E")))))

test date diff = 1.*([max prod date]-[Minimum Prod date])

max prod date = calculate(max('Table'[Production Date-Time]),ALLSELECTED('Table'[Production Date-Time]))

Minimum Prod date = calculate(min('Table'[Production Date-Time]),ALLSELECTED('Table'[Production Date-Time]))

link to pbi file:

https://kimberlyclark-my.sharepoint.com/:u:/r/personal/aneesh_vs_kcc_com/Documents/Not-able-to-selec...

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculated table and  use a measure in Visual Filter to meet your requirement:

Calculated table (Make "Axis" Column sort by "Sort" column):

Axis Table =
VAR t =
    ADDCOLUMNS (
        CROSSJOIN (
            SELECTCOLUMNS ( { "D", "E", "H", "M" }, "Level", [Value] ),
            DISTINCT ( 'Table'[Production Date-Time] )
        ),
        "Axis", SWITCH (
            [Level],
            "D", FORMAT ( [Production Date-Time], "YYYY-MM-DD" ),
            "E", FORMAT ( [Production Date-Time], "YYYY-MM-DD HH:MM:SS" ),
            "H", FORMAT ( [Production Date-Time], "H" ),
            "M", FORMAT ( [Production Date-Time], "MMM DD" )
        )
    )
RETURN
    ADDCOLUMNS (
        t,
        "Sort",
        VAR p = [Production Date-Time]
        RETURN
            SWITCH (
                [Level],
                "D", RANKX (
                    SELECTCOLUMNS (
                        t,
                        "Date", VALUE ( FORMAT ( [Production Date-Time], "YYYYMMDD" ) )
                    ),
                    VALUE ( FORMAT ( p, "YYYYMMDD" ) ),
                    ,
                    ASC,
                    DENSE
                ),
                "E", RANKX (
                    SELECTCOLUMNS (
                        t,
                        "Date", VALUE ( FORMAT ( [Production Date-Time], "YYYYMMDDHHMMSS" ) )
                    ),
                    VALUE ( FORMAT ( p, "YYYYMMDDHHMMSS" ) ),
                    ,
                    ASC,
                    DENSE
                ),
                "H", RANKX (
                    SELECTCOLUMNS ( t, "Date", VALUE ( FORMAT ( [Production Date-Time], "HH" ) ) ),
                    VALUE ( FORMAT ( p, "HH" ) ),
                    ,
                    ASC,
                    DENSE
                ),
                "M", RANKX (
                    SELECTCOLUMNS ( t, "Date", VALUE ( FORMAT ( [Production Date-Time], "MMDD" ) ) ),
                    VALUE ( FORMAT ( p, "MMDD" ) ),
                    ,
                    ASC,
                    DENSE
                )
            )
    )

Measure (set as "is 1" in visual filter):

Visual Control = IF(max('Axis Table'[Level]) = CALCULATE([Time level],ALLSELECTED()), 1 ,-1)

We have deleted the slicer table:

By the way, PBIX file as attached.

Best regards,

[Anonymous]

This is just amazing!! I was trying to figure this out from the past 3 days, Thanks a lot!

[Mariusz]

Hi @Anonymous 

Columns are not dynamic, so you will not be able to reflect slicer selection in a column.

You can research this article, adjust and apply to your scenario.

https://www.daxpatterns.com/dynamic-segmentation/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn