Forum Discussion

asparagus1_'s avatar
asparagus1_
Helper I
4 months ago
Solved

Parameter table dimensions - summarize

Hi,

I have a parameter table that lets users dynamically choose a dimension (e.g., product category, product family, etc.), which changes how the data is sliced in a visual.

I’d like to calculate a total that always matches what’s currently visible in the table, regardless of the selected dimension.

Ideally something like:
SUMX(
SUMMARIZECOLUMNS(dynamic columns),
[Measure]
)

But since the columns would need to change dynamically, I’m not sure if this is achievable in DAX.

Is there a way to achive this or a better workaround? (Without manually listing all the possible combinations)

Thanks in advance!

Need Help
  • danextian's avatar
    danextian
    4 months ago

    Field parameters aren’t actual columns, so they can’t be used directly inside SUMMARIZE. They act more like a dynamic dimension selector. To work with the selected field, create a conditional measure that responds to the chosen parameter value.

     

    Example:

     

    VAR _order =
    SELECTEDVALUE ( FieldParameter[Order] )

RETURN
SWITCH (
    _order,
    1,
        SUMX (
            SUMMARIZECOLUMNS (
                'Table'[Column1],
                "@value", [The Measure]
            ),
            [@value]
        ),
    2,
        SUMX (
            SUMMARIZECOLUMNS (
                'Table'[Column2],
                "@value", [The Measure]
            ),
            [@value]
        ),
    3,
        SUMX (
            SUMMARIZECOLUMNS (
                'Table'[Column3],
                "@value", [The Measure]
            ),
            [@value]
        )
)

6 Replies

  • Thomaslleblanc's avatar
    Thomaslleblanc
    Super User
    4 months ago

    You cannot have a dynamic column in a measure using SUMMARIZECOLUMNS

    You could use a more complicated method like a mapping table and the TREATAS function, but that is more complicated than a simplae measure

     

  • lbendlin's avatar
    lbendlin
    Super User
    4 months ago
    I’d like to calculate a total that always matches what’s currently visible in the table

    You seem to be looking for Visual Calculations.

  • danextian's avatar
    danextian
    Super User
    4 months ago

    Field parameters aren’t actual columns, so they can’t be used directly inside SUMMARIZE. They act more like a dynamic dimension selector. To work with the selected field, create a conditional measure that responds to the chosen parameter value.

     

    Example:

     

    VAR _order =
    SELECTEDVALUE ( FieldParameter[Order] )

RETURN
SWITCH (
    _order,
    1,
        SUMX (
            SUMMARIZECOLUMNS (
                'Table'[Column1],
                "@value", [The Measure]
            ),
            [@value]
        ),
    2,
        SUMX (
            SUMMARIZECOLUMNS (
                'Table'[Column2],
                "@value", [The Measure]
            ),
            [@value]
        ),
    3,
        SUMX (
            SUMMARIZECOLUMNS (
                'Table'[Column3],
                "@value", [The Measure]
            ),
            [@value]
        )
)
  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    4 months ago

    Hi asparagus1_ 

    Thank you for submitting your question to the Microsoft Fabric Community Forum. Also, thanks also to Thomaslleblanc , lbendlin , danextian and  Kedar_Pande  for providing helpful suggestions.

    Could you let us know if the suggested solution resolved your issue? If not, please share any additional details so we can assist further.

    Best regards,
    Community Support Team.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    4 months ago

    Hi asparagus1_ 

    We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

     

    Thank You.