Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Rasmus_Schwerin
Frequent Visitor

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 currently focusing on making it more dynamic.

 

Today they have two tables: One that contains traditional fact data such as revenue etc. and the other table contains different % values for every product, split in multiple different columns for different periods. 

 

Tables Examples:

Revenue Table

ProductRevenue

A

100
B50
C150

 

Rates Table

ProductJan-AprMay-AugSep - Dec

A

90%81%98%
B89%85%98%
C75%96%93%

 

What I am trying to accomplish is to have a slicer in the report where the user can select the period they want to see calculations for and the Calculation updates accordingly.  

 

If the user selects Jan-Apr it should return as:

 

ProductCalculated Value

A

90% * 100
B89% * 50
C75% * 150

and the corresponding column values if they select May-Aug instead

 

Creating the measure for multiplying the data isn't a problem, the IF statement is there to handle some unexpected missing rates: 

 

 

 

 

SUMX(
    Sales, 
    Sales[Revenue] * 
    CALCULATE(
        IF(
            FIRSTNONBLANK('Annualization Factors'[Jan - Apr], 1)=0, 1, 
            FIRSTNONBLANK('Annualization Factors'[Jan - Apr], 1)
            )
        )
    )

 

 

 

 

Conceptually it feels like it is something Field Parameters should be able to handle, simply replacing the "Annualization Factors[Jan-Apr]" part. However, I haven't been able to make this work.

 

Is there a way to utilize Field Parameters for this or any other way to solve my issue? Any help is appreciated

 

 

1 ACCEPTED SOLUTION

@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

 

DataInsights_0-1675174059922.png

 

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:

 

DataInsights_1-1675174324824.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Rasmus_Schwerin
Frequent Visitor

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

@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

 

DataInsights_0-1675174059922.png

 

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:

 

DataInsights_1-1675174324824.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you for your assistance, really appreciated.

 

Will use your code to develop my understanding for the future

 

Best,

Rasmus

DataInsights
Super User
Super User

@Rasmus_Schwerin,

 

A different approach is to unpivot the Annualization Factors table so it looks like this:

 

DataInsights_0-1675095162931.png

 

Data model:

 

DataInsights_1-1675095181378.png

 

Measure:

 

Calculated Value = 
SUMX (
    Sales,
    Sales[Revenue]
        * MAXX (
            RELATEDTABLE ( 'Annualization Factors' ),
            'Annualization Factors'[Value]
        )
)

 

Use Sales[Product] in visual:

 

DataInsights_2-1675095233168.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@DataInsights Thank you, pivoting works for my purpose but causes another issue due to changing the data structure. 

 

The functionality is there and looks to be working as I wanted it to. However, the relationship between the revenue table and the annualization goes to a many-to-many instead of a one-to-many. 

 

There are multiple lines for the same product on the revenue table, and unpivoting it creates multiple versions of a Product on both sides. I realize now that I should have included that in the sample table to avoid confusion.

While the slicer works perfectly in changing the rates, it creates an issue where there is Revenue for a Product, but it can't find a rate on the other side of the relationship. When selecting a period in the slicer, any Product without that relationship is filtered out, I would like it to be multiplied by 1 if the rate can't be found instead of being filtered out completel 

 

Example:

ProductRevenueRateRevenue Annualized
Product A50050
Product B60060

Dexcom lacks the rate, but has claims in this case. If a slicer value is selected these 2 rows disappear completely instead of returning the Revenue value


Can the measure be modified to handle this or is there another way to tackle the data model?

 

Thank you for your assistance!

Rasmus

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.