Using parameter to select column and table

[Ara_Karapetyan]

Hi everyone.
I am trying to create a mesure for Pareto diagram.

The measure is:

Pareto =

var _totalsales = CALCULATE([Sales amount total], ALLSELECTED(FactSales))

var _currentsales = [Sales amount total]

var _table =

    SUMMARIZE(

        ALLSELECTED(FactSales),

        'DimProductCategory'[ProductCategoryName],

        "sales", [Sales amount total]

    )

var _cumsales =

    SUMX(

        FILTER(

            _table,

            [Sales amount total] >= _currentsales

        ),

        [Sales amount total]

    )

return

_cumsales/_totalsales

Everything works well when I use a static table_column_name for X axis.

I need to have an ability to select X axis between product category, product subcategory and product description, those are different tables.

For that purpose I have created a parameter to use in slicer for selection:
Pareto X axis = {

    ("ProductCategoryName", NAMEOF('DimProductCategory'[ProductCategoryName]), 0),

    ("ProductSubcategoryName", NAMEOF('DimProductSubcategory'[ProductSubcategoryName]), 1),

    ("ProductDescription", NAMEOF('DimProduct'[ProductDescription]), 2)

}

Now when I put that parameter to select column for Summarize function I get an error:

SUMMARIZE(

        ALLSELECTED(FactSales),

        'Pareto X axis'[Pareto X axis],

        "sales", [Sales amount total]

    )

Power BI underlines the row. I cant use that construction.

All the three dim tables are connected to the Factsales table.

Any ideas?

[Anonymous]

Hi @Ara_Karapetyan ,

According to your statement, I think [Pareto X axis] table is not related to your fact table. So your code will return error.

If you want to show three measures by measure name, I suggest you to try SWITCH() function.

Firstly, I suggest you to inactive the relationship between your fact table and dimtables.

Data model:

Measure:

Switch Measure = 
SWITCH (
    SELECTEDVALUE ( 'Pareto X axis'[ID] ),
    0,
        CALCULATE (
            SUM ( FactSales[Sales] ),
            FILTER (
                FactSales,
                FactSales[ProductCategoryName]
                    IN VALUES ( DimProductCategory[ProductCategoryName] )
            )
        ),
    1,
        CALCULATE (
            SUM ( FactSales[Sales] ),
            FILTER (
                FactSales,
                FactSales[ProductSubcategoryName]
                    IN VALUES ( DimProductSubcategory[ProductSubcategoryName] )
            )
        ),
    2,
        CALCULATE (
            SUM ( FactSales[Sales] ),
            FILTER (
                FactSales,
                FactSales[ProductDescription] IN VALUES ( DimProduct[ProductDescription] )
            )
        )
)

Result is as below.

Best Regards,
Rico Zhou

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

[Ara_Karapetyan]

Thank you for your reply.

My problem is with SUMMARIZE not SUM.