Forum Discussion

datawomen's avatar
datawomen
Regular Visitor
3 years ago
Solved

Dax table

i am trying to build a table from a variable created inside a measure. I basically wants to select a maximum of the maximum values. My best thought was to create a variable for  each column then feed all these values into one table column and select a max out of there. the challenge i am facing is that inside table constructor, i don't know  how to assign a column name . I tried datatable but it does not feed variable values. Any help will be appreciated.

 

Most common idle code =
Var Maxvalue1 = MAX('Agent IdleTimes'[Bathroom Break - Count])
Var Maxvalue2 = MAX('Agent IdleTimes'[Boards - Dispatch - Count])
Var Maxvalue3 = MAX('Agent IdleTimes'[Break - 15 Mins - Count])
Var Maxvalue4 = MAX('Agent IdleTimes'[Customer Call Back - Count])
Var Maxvalue5 = MAX('Agent IdleTimes'[Dispatch Support - Outbound Calls - Count])
Var Maxvalue6 = MAX('Agent IdleTimes'[Email - Count])
Var Maxvalue7 = MAX('Agent IdleTimes'[IT Issues - Support - Count])
Var Maxvalue8 = MAX('Agent IdleTimes'[Incident Report - Dispatch Only -Count])
Var Maxvalue9 = MAX('Agent IdleTimes'[Lunch - 30 Mins - Count])
Var Maxvalue10 = MAX('Agent IdleTimes'[Meeting - Count])
Var Maxvalue11 = MAX('Agent IdleTimes'[Segment Alerts - Faxes - Count])
Var Maxvalue12 = MAX('Agent IdleTimes'[Supervisor Support - Armand - Count])
Var Maxvalue13 = MAX('Agent IdleTimes'[Training - Count])
Var Maxvalue14 = MAX('Agent IdleTimes'[Unavailable - Logged In - Count])
Var Maxvalue15 = MAX('Agent IdleTimes'[Wrap up - Extended - Count])
Var table1 = DATATABLE("maxvalues",INTEGER,{{Maxvalue1,Maxvalue2,Maxvalue3,Maxvalue4,Maxvalue5,Maxvalue6,Maxvalue7,Maxvalue8,Maxvalue9,Maxvalue10,Maxvalue11,Maxvalue12,Maxvalue13,Maxvalue14,Maxvalue15}})

RETURN
max(table1)
Need Help
  • sevenhills's avatar
    sevenhills
    3 years ago

    I am not sure whether you are looking for a measure or dax data table syntax. I am providing both syntax.

     

    Measure to get max of max:

     

    Max Measure Name = 
MAXX(
	{
		  MAX('Agent IdleTimes'[Bathroom Break - Count])
		, MAX('Agent IdleTimes'[Boards - Dispatch - Count])
		, MAX('Agent IdleTimes'[Break - 15 Mins - Count])
		, MAX('Agent IdleTimes'[Customer Call Back - Count])
		, MAX('Agent IdleTimes'[Dispatch Support - Outbound Calls - Count])
		, MAX('Agent IdleTimes'[Email - Count])
		, MAX('Agent IdleTimes'[IT Issues - Support - Count])
		, MAX('Agent IdleTimes'[Incident Report - Dispatch Only -Count])
		, MAX('Agent IdleTimes'[Lunch - 30 Mins - Count])
		, MAX('Agent IdleTimes'[Meeting - Count])
		, MAX('Agent IdleTimes'[Segment Alerts - Faxes - Count])
		, MAX('Agent IdleTimes'[Supervisor Support - Armand - Count])
		, MAX('Agent IdleTimes'[Training - Count])
		, MAX('Agent IdleTimes'[Unavailable - Logged In - Count])
		, MAX('Agent IdleTimes'[Wrap up - Extended - Count])
	}
	,
    [Value]
)

     

     

    Table with just one column and one row with max of maxes 

    Click New table and paste this dax

     

    Table 1 =  ROW ( "Max Measure Name", 
MAXX(
	{
		  MAX('Agent IdleTimes'[Bathroom Break - Count])
		, MAX('Agent IdleTimes'[Boards - Dispatch - Count])
		, MAX('Agent IdleTimes'[Break - 15 Mins - Count])
		, MAX('Agent IdleTimes'[Customer Call Back - Count])
		, MAX('Agent IdleTimes'[Dispatch Support - Outbound Calls - Count])
		, MAX('Agent IdleTimes'[Email - Count])
		, MAX('Agent IdleTimes'[IT Issues - Support - Count])
		, MAX('Agent IdleTimes'[Incident Report - Dispatch Only -Count])
		, MAX('Agent IdleTimes'[Lunch - 30 Mins - Count])
		, MAX('Agent IdleTimes'[Meeting - Count])
		, MAX('Agent IdleTimes'[Segment Alerts - Faxes - Count])
		, MAX('Agent IdleTimes'[Supervisor Support - Armand - Count])
		, MAX('Agent IdleTimes'[Training - Count])
		, MAX('Agent IdleTimes'[Unavailable - Logged In - Count])
		, MAX('Agent IdleTimes'[Wrap up - Extended - Count])
	}
	,
    [Value]
)

)

     

     

    Hope it helps!

1 Reply

  • sevenhills's avatar
    sevenhills
    Super User
    3 years ago

    I am not sure whether you are looking for a measure or dax data table syntax. I am providing both syntax.

     

    Measure to get max of max:

     

    Max Measure Name = 
MAXX(
	{
		  MAX('Agent IdleTimes'[Bathroom Break - Count])
		, MAX('Agent IdleTimes'[Boards - Dispatch - Count])
		, MAX('Agent IdleTimes'[Break - 15 Mins - Count])
		, MAX('Agent IdleTimes'[Customer Call Back - Count])
		, MAX('Agent IdleTimes'[Dispatch Support - Outbound Calls - Count])
		, MAX('Agent IdleTimes'[Email - Count])
		, MAX('Agent IdleTimes'[IT Issues - Support - Count])
		, MAX('Agent IdleTimes'[Incident Report - Dispatch Only -Count])
		, MAX('Agent IdleTimes'[Lunch - 30 Mins - Count])
		, MAX('Agent IdleTimes'[Meeting - Count])
		, MAX('Agent IdleTimes'[Segment Alerts - Faxes - Count])
		, MAX('Agent IdleTimes'[Supervisor Support - Armand - Count])
		, MAX('Agent IdleTimes'[Training - Count])
		, MAX('Agent IdleTimes'[Unavailable - Logged In - Count])
		, MAX('Agent IdleTimes'[Wrap up - Extended - Count])
	}
	,
    [Value]
)

     

     

    Table with just one column and one row with max of maxes 

    Click New table and paste this dax

     

    Table 1 =  ROW ( "Max Measure Name", 
MAXX(
	{
		  MAX('Agent IdleTimes'[Bathroom Break - Count])
		, MAX('Agent IdleTimes'[Boards - Dispatch - Count])
		, MAX('Agent IdleTimes'[Break - 15 Mins - Count])
		, MAX('Agent IdleTimes'[Customer Call Back - Count])
		, MAX('Agent IdleTimes'[Dispatch Support - Outbound Calls - Count])
		, MAX('Agent IdleTimes'[Email - Count])
		, MAX('Agent IdleTimes'[IT Issues - Support - Count])
		, MAX('Agent IdleTimes'[Incident Report - Dispatch Only -Count])
		, MAX('Agent IdleTimes'[Lunch - 30 Mins - Count])
		, MAX('Agent IdleTimes'[Meeting - Count])
		, MAX('Agent IdleTimes'[Segment Alerts - Faxes - Count])
		, MAX('Agent IdleTimes'[Supervisor Support - Armand - Count])
		, MAX('Agent IdleTimes'[Training - Count])
		, MAX('Agent IdleTimes'[Unavailable - Logged In - Count])
		, MAX('Agent IdleTimes'[Wrap up - Extended - Count])
	}
	,
    [Value]
)

)

     

     

    Hope it helps!