Hello,I have a working Pareto measure with one "sub category" working, but I can't change that to dynamic working with a field parameterhere is the measure Pareto % =IF( 1, //ISINSCOPE(par_stop_category[par_stop_category Name]), VAR __AllselectedStops = ALLSELECTED(dim_stop_code[hrm_description]) VAR __StopTable = ADDCOLUMNS(__AllselectedStops, "@Count", [Stops #]) VAR __CurrentCount = [Stops #] VAR __CumStopTable = FILTER(__StopTable, [@Count] >= __CurrentCount) VAR __CumStop = SUMX( __CumStopTable, [@Count]) VAR __AllSelectedStopsCount = CALCULATE([Stops #], __AllselectedStops) VAR __Result = DIVIDE(__CumStop, __AllSelectedStopsCount) RETURN IF(__CurrentCount > 0, __Result, BLANK()))my field parameter look like this par_stop_category = { ("Main", NAMEOF('dim_stop_code'[mn_description]), 1,"Main"), ("Harm", NAMEOF('dim_stop_code'[hrm_description]), 0,"Harm"), ("Place", NAMEOF('dim_stop_code'[plc_description]), 2,"Place"), ("Reason", NAMEOF('dim_stop_code'[rsn_description]), 3,"Reason"), ("Code", NAMEOF('dim_stop_code'[code_description]), 5,"Code"), ("Category", NAMEOF('dim_stop_code'[ctgry_description]), -1,"Category"), ("Group", NAMEOF('dim_stop_code'[grp_description]), 1,"Group"), //ERROR ("Error Place", NAMEOF('dim_error_code'[erplc_description]), 8,"Error Place"), ("Error Code", NAMEOF('dim_error_code'[errcd_description]), 9,"Error Code")} both are working fine with themselfes, but when I try to create a dynamic pareto, it's not working any more cause of the error "The ADDCOLUMNS function expects a table expression for argument '', but a string or numeric expression was used."here my dynamic Pareto MeasurePareto % =IF( 1, //ISINSCOPE(par_stop_category[par_stop_category Name]), // VAR __AllselectedStops = ALLSELECTED(dim_stop_code[hrm_description]) VAR __AllselectedStops = SWITCH( SELECTEDVALUE(par_stop_category[par_stop_category Name]), "Main", ALLSELECTED(dim_stop_code[mn_description]), "Harm", ALLSELECTED(dim_stop_code[hrm_description]), "Place", ALLSELECTED(dim_stop_code[plc_description]), "Reason", ALLSELECTED(dim_stop_code[rsn_description]), "Code", ALLSELECTED(dim_stop_code[code_description]), "Category", ALLSELECTED(dim_stop_code[ctgry_description]), "Group", ALLSELECTED(dim_stop_code[mn_description]), "Error Place", ALLSELECTED(dim_error_code[erplc_description]), "Error Code", ALLSELECTED(dim_error_code[errcd_description]) ) VAR __StopTable = ADDCOLUMNS(__AllselectedStops, "@Count", [Stops #]) VAR __CurrentCount = [Stops #] VAR __CumStopTable = FILTER(__StopTable, [@Count] >= __CurrentCount) VAR __CumStop = SUMX( __CumStopTable, [@Count]) VAR __AllSelectedStopsCount = CALCULATE([Stops #], __AllselectedStops) VAR __Result = DIVIDE(__CumStop, __AllSelectedStopsCount) RETURN IF(__CurrentCount > 0, __Result, BLANK()))maybe somebody have some good ideas 🙂

Hello, You can use visual "Pareto+" to create a graph and table. The visual with do all of the calculations for you. You can also use "Pareto by sio2Graphs" to produce the graph, no need for measures. 😀sio2Graphssio2graphs on AppSource

Dynamic Pareto chart - with field parameters not working

Pareto % =

IF(
1, //ISINSCOPE(par_stop_category[par_stop_category Name]),
VAR __AllselectedStops = ALLSELECTED(dim_stop_code[hrm_description])
VAR __StopTable = ADDCOLUMNS(__AllselectedStops, "@Count", [Stops #])
VAR __CurrentCount = [Stops #]
VAR __CumStopTable = FILTER(__StopTable, [@Count] >= __CurrentCount)
VAR __CumStop = SUMX( __CumStopTable, [@Count])
VAR __AllSelectedStopsCount = CALCULATE([Stops #], __AllselectedStops)
VAR __Result = DIVIDE(__CumStop, __AllSelectedStopsCount)
RETURN
IF(__CurrentCount > 0, __Result, BLANK())
)

par_stop_category = {
("Main", NAMEOF('dim_stop_code'[mn_description]), 1,"Main"),
("Harm", NAMEOF('dim_stop_code'[hrm_description]), 0,"Harm"),
("Place", NAMEOF('dim_stop_code'[plc_description]), 2,"Place"),
("Reason", NAMEOF('dim_stop_code'[rsn_description]), 3,"Reason"),
("Code", NAMEOF('dim_stop_code'[code_description]), 5,"Code"),
("Category", NAMEOF('dim_stop_code'[ctgry_description]), -1,"Category"),
("Group", NAMEOF('dim_stop_code'[grp_description]), 1,"Group"),
//ERROR
("Error Place", NAMEOF('dim_error_code'[erplc_description]), 8,"Error Place"),
("Error Code", NAMEOF('dim_error_code'[errcd_description]), 9,"Error Code")
}

Pareto % =

IF(
1, //ISINSCOPE(par_stop_category[par_stop_category Name]),
// VAR __AllselectedStops = ALLSELECTED(dim_stop_code[hrm_description])
VAR __AllselectedStops =
SWITCH(
SELECTEDVALUE(par_stop_category[par_stop_category Name]),
"Main", ALLSELECTED(dim_stop_code[mn_description]),
"Harm", ALLSELECTED(dim_stop_code[hrm_description]),
"Place", ALLSELECTED(dim_stop_code[plc_description]),
"Reason", ALLSELECTED(dim_stop_code[rsn_description]),
"Code", ALLSELECTED(dim_stop_code[code_description]),
"Category", ALLSELECTED(dim_stop_code[ctgry_description]),
"Group", ALLSELECTED(dim_stop_code[mn_description]),
"Error Place", ALLSELECTED(dim_error_code[erplc_description]),
"Error Code", ALLSELECTED(dim_error_code[errcd_description])
)
VAR __StopTable = ADDCOLUMNS(__AllselectedStops, "@Count", [Stops #])
VAR __CurrentCount = [Stops #]
VAR __CumStopTable = FILTER(__StopTable, [@Count] >= __CurrentCount)
VAR __CumStop = SUMX( __CumStopTable, [@Count])
VAR __AllSelectedStopsCount = CALCULATE([Stops #], __AllselectedStops)
VAR __Result = DIVIDE(__CumStop, __AllSelectedStopsCount)
RETURN
IF(__CurrentCount > 0, __Result, BLANK())
)

6 Replies

cousinitt13

Helper I

1 year ago

's not perfect, but I'll figured it out with a different way
I'm calculating the Pareto for all categories first, and do the switch within the main PARETO measure

Pareto % =
    SWITCH(
        SELECTEDVALUE(par_stop_category[par_stop_category Name]),
        "Main", [Pareto (mn) %],
        "Harm", [Pareto (hrm) %],
        "Place", [Pareto (plc) %],
        "Reason", [Pareto (rsn) %],
        "Code", [Pareto (code) %],
        "Category", [Pareto (ctg) %],
        "Group", [Pareto (grp) %]
        //"Error Place", ALLSELECTED(dim_error_code[erplc_description]),
        //"Error Code", ALLSELECTED(dim_error_code[errcd_description])
    )

sio2Graphs
Helper I
1 year ago
Hello,

You can use visual "Pareto+" to create a graph and table. The visual with do all of the calculations for you. You can also use "Pareto by sio2Graphs" to produce the graph, no need for measures.

😀
sio2Graphs
sio2graphs on AppSource
bhanu_gautam
Super User
1 year ago
cousinitt13 , Try using below DAX

Pareto % =
IF(
1, //ISINSCOPE(par_stop_category[par_stop_category Name]),
VAR __AllselectedStops =
SWITCH(
SELECTEDVALUE(par_stop_category[par_stop_category Name]),
"Main", SUMMARIZE(dim_stop_code, dim_stop_code[mn_description]),
"Harm", SUMMARIZE(dim_stop_code, dim_stop_code[hrm_description]),
"Place", SUMMARIZE(dim_stop_code, dim_stop_code[plc_description]),
"Reason", SUMMARIZE(dim_stop_code, dim_stop_code[rsn_description]),
"Code", SUMMARIZE(dim_stop_code, dim_stop_code[code_description]),
"Category", SUMMARIZE(dim_stop_code, dim_stop_code[ctgry_description]),
"Group", SUMMARIZE(dim_stop_code, dim_stop_code[grp_description]),
"Error Place", SUMMARIZE(dim_error_code, dim_error_code[erplc_description]),
"Error Code", SUMMARIZE(dim_error_code, dim_error_code[errcd_description])
)
VAR __StopTable = ADDCOLUMNS(__AllselectedStops, "@Count", [Stops #])
VAR __CurrentCount = [Stops #]
VAR __CumStopTable = FILTER(__StopTable, [@Count] >= __CurrentCount)
VAR __CumStop = SUMX( __CumStopTable, [@Count])
VAR __AllSelectedStopsCount = CALCULATE([Stops #], __AllselectedStops)
VAR __Result = DIVIDE(__CumStop, __AllSelectedStopsCount)
RETURN
IF(__CurrentCount > 0, __Result, BLANK())
)
- cousinitt13
  Helper I
  1 year ago
  thanks, but still getting this error
  "
  The ADDCOLUMNS function expects a table expression for argument '', but a string or numeric expression was used."
  - bhanu_gautam
    Super User
    1 year ago
    cousinitt13 , Try using updated one
    
    Pareto % =
    IF(
    1, //ISINSCOPE(par_stop_category[par_stop_category Name]),
    VAR __SelectedColumn =
    SWITCH(
    SELECTEDVALUE(par_stop_category[par_stop_category Name]),
    "Main", dim_stop_code[mn_description],
    "Harm", dim_stop_code[hrm_description],
    "Place", dim_stop_code[plc_description],
    "Reason", dim_stop_code[rsn_description],
    "Code", dim_stop_code[code_description],
    "Category", dim_stop_code[ctgry_description],
    "Group", dim_stop_code[grp_description],
    "Error Place", dim_error_code[erplc_description],
    "Error Code", dim_error_code[errcd_description]
    )
    VAR __AllselectedStops = SUMMARIZE(ALLSELECTED(__SelectedColumn), __SelectedColumn)
    VAR __StopTable = ADDCOLUMNS(__AllselectedStops, "@Count", [Stops #])
    VAR __CurrentCount = [Stops #]
    VAR __CumStopTable = FILTER(__StopTable, [@Count] >= __CurrentCount)
    VAR __CumStop = SUMX(__CumStopTable, [@Count])
    VAR __AllSelectedStopsCount = CALCULATE([Stops #], ALLSELECTED(__SelectedColumn))
    VAR __Result = DIVIDE(__CumStop, __AllSelectedStopsCount)
    RETURN
    IF(__CurrentCount > 0, __Result, BLANK())
    ) --
    
    If you still face issue share sample data
Anonymous
Not applicable
1 year ago
Hi cousinitt13

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

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

Forum Discussion

Dynamic Pareto chart - with field parameters not working

6 Replies

Related content

Dynamic M Query Parameters not working when I make slicer selection

Sum dynamic parameter fields multiple selected parameter

Dynamic subcription parameter not saving

Dynamic M parameter is not working

Decimal level parameter not working