Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
)
)
Solved! Go to 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,
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,
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.
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,
This is just amazing!! I was trying to figure this out from the past 3 days, Thanks a lot!
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |