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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
dwel0120
Helper III
Helper III

Measure with an IF Statement and Columns

I have a table that contains a report name a metric and a definition (example below). I want to create a measure that basically says: If the Report_Name = Finance && the Metric = Sales then it is the Definition but I can't quite figure out how to write that formula. There are duplicate report_names, metrics, and definitions in my table but each line is unique. My goal is to create a button that when the end user hovers over the metric button, it shows the definition of that metric. 

 

 

Report_NameMetricDefinition
FinanceSalestransaction between two or more parties that involves the exchange of tangible or intangible goods, services, or assets for money
Financeearnings before interestmeasure of a firm's profit that includes all incomes and expenses except interest expenses and income tax expenses
Financeefficiency ratiosexpenses as a percentage of revenue, with a few variations
ProductionSalestransaction between two or more parties that involves the exchange of tangible or intangible goods, services, or assets for money
ProductionProduction Volumesthe percent of the average volume of production of the affected commodity of those on the list of affected parties or affected producers for a production period.
BillingSalestransaction between two or more parties that involves the exchange of tangible or intangible goods, services, or assets for money
BillingMonthly Recurring Revenue the predictable total revenue generated by your business from all the active subscriptions in a particular month
BillingRenewal RateRenewal rate measures the percentage of customers who renew their contracts at the end of their subscriptions. Going by the definition, you might think that the renewal rate is a straightforward concept
2 REPLIES 2
Anonymous
Not applicable

Hi @dwel0120 ,

Please have a try.

Create a column.

Column =
IF (
    ( 'Table'[Report_Name] ) = "Finance"
        && ( 'Table'[Metric] ) = "earnings before interest",
    "measure of a firm's profit that includes all incomes and expenses except interest expenses and income tax expenses",
    IF (
        ( 'Table'[Report_Name] ) = "Finance"
            && ( 'Table'[Metric] ) = "efficiency ratios",
        "expenses as a percentage of revenue, with a few variations",
        IF (
            ( 'Table'[Report_Name] ) = "Finance"
                && ( 'Table'[Metric] ) = "Sales",
            "transaction between two or more parties that involves the exchange of tangible or intangible goods, services, or assets for money",
            IF (
                ( 'Table'[Report_Name] ) = "Billing"
                    && ( 'Table'[Metric] ) = "Renewal Rate",
                "Renewal rate measures the percentage of customers who renew their contracts at the end of their subscriptions. Going by the definition, you might think that the renewal rate is a straightforward concept",
                IF (
                    ( 'Table'[Report_Name] ) = "Billing"
                        && ( 'Table'[Metric] ) = "Monthly Recurring Revenue",
                    "the predictable total revenue generated by your business from all the active subscriptions in a particular month",
                    IF (
                        ( 'Table'[Report_Name] ) = "Billing"
                            && ( 'Table'[Metric] ) = "Sales",
                        "transaction between two or more parties that involves the exchange of tangible or intangible goods, services, or assets for money",
                        IF (
                            ( 'Table'[Report_Name] ) = "Production"
                                && ( 'Table'[Metric] ) = "Production Volumes",
                            "the percent of the average volume of production of the affected commodity of those on the list of affected parties or affected producers for a production period.",
                            IF (
                                ( 'Table'[Report_Name] ) = "Production"
                                    && ( 'Table'[Metric] ) = "Sales",
                                "transaction between two or more parties that involves the exchange of tangible or intangible goods, services, or assets for money",
                                BLANK ()
                            )
                        )
                    )
                )
            )
        )
    )
)

 

Then set a tooltip.

Create report tooltip pages in Power BI - Power BI | Microsoft Learn

 

vpollymsft_0-1666334091508.png

 

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

 

Best Regards

Community Support Team _ Polly

 

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

That will not work because definitions change all the time. 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.