Dynamic Pareto chart - with field parameters not working

[cousinitt13]

Hello,

I have a working Pareto measure with one "sub category" working, but I can't change that to dynamic working with a field parameter

here 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 Measure

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())
)

maybe somebody have some good ideas 🙂

[Anonymous]

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.

[sio2Graphs · Accepted solution]

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 

[cousinitt13]

'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])
    )

[bhanu_gautam]

@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]

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]

@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