Forum Discussion

Rasmus_Schwerin's avatar
Rasmus_Schwerin
Frequent Visitor
3 years ago
Solved

Selecting Column Dynamically for a measure calculation with a Slicer

  Hi,   Been banging my head against the wall for some time now but I seem to have run into a dead end for my issue.   I am rebuilding a manual Finance Forecast with the help of Power BI and am ...
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    Rasmus_Schwerin,

     

    A bridge table is a viable option for handling many-to-many relationships. Here's a different approach that allows you to use the data model in my example, but without the relationship. I added rows to Sales such that each Product has multiple rows, and Product D doesn't have a row in Annualization Factors.

     

    Calculated Value = 
VAR vDateRange =
    SELECTEDVALUE ( 'Annualization Factors'[Date Range] )
VAR vTable =
    ADDCOLUMNS (
        VALUES ( Sales[Product] ),
        "@Revenue", CALCULATE ( SUM ( Sales[Revenue] ) ),
        "@Factor",
            VAR vProduct = Sales[Product]
            RETURN
            CALCULATE (
                MAX ( 'Annualization Factors'[Value] ),
                'Annualization Factors'[Product] = vProduct,
                'Annualization Factors'[Date Range] = vDateRange
            )
    )
VAR vResult =
    SUMX ( vTable, [@Revenue] * COALESCE ( [@Factor], 1 ) )
RETURN
    vResult

     

     

    In case you're interested, a useful technique during testing is to replace the SUMX with the line below:

     

    CONCATENATEX ( vTable, [@Revenue] & " | " & COALESCE ( [@Factor], 1 ), ", " )

     

    This allows you to see the underlying components of each data point in the matrix:

     

     

Rasmus_Schwerin's avatar
Rasmus_Schwerin
Frequent Visitor
3 years ago

I believe I figured out a workaround. I kept the original table unpivoted and had it act as a bridge table to the unpivoted one. That way I didn't break the one-to-many relationship and the measure now acts as intended. 

 

Unsure if this is best practice however

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
3 years ago

Rasmus_Schwerin,

 

A bridge table is a viable option for handling many-to-many relationships. Here's a different approach that allows you to use the data model in my example, but without the relationship. I added rows to Sales such that each Product has multiple rows, and Product D doesn't have a row in Annualization Factors.

 

Calculated Value = 
VAR vDateRange =
    SELECTEDVALUE ( 'Annualization Factors'[Date Range] )
VAR vTable =
    ADDCOLUMNS (
        VALUES ( Sales[Product] ),
        "@Revenue", CALCULATE ( SUM ( Sales[Revenue] ) ),
        "@Factor",
            VAR vProduct = Sales[Product]
            RETURN
            CALCULATE (
                MAX ( 'Annualization Factors'[Value] ),
                'Annualization Factors'[Product] = vProduct,
                'Annualization Factors'[Date Range] = vDateRange
            )
    )
VAR vResult =
    SUMX ( vTable, [@Revenue] * COALESCE ( [@Factor], 1 ) )
RETURN
    vResult

 

 

In case you're interested, a useful technique during testing is to replace the SUMX with the line below:

 

CONCATENATEX ( vTable, [@Revenue] & " | " & COALESCE ( [@Factor], 1 ), ", " )

 

This allows you to see the underlying components of each data point in the matrix:

 

 

  • Rasmus_Schwerin's avatar
    Rasmus_Schwerin
    Frequent Visitor
    3 years ago

    Thank you for your assistance, really appreciated.

     

    Will use your code to develop my understanding for the future

     

    Best,

    Rasmus