Measure with an IF Statement and Columns

[dwel0120]

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_Name	Metric	Definition
Finance	Sales	transaction between two or more parties that involves the exchange of tangible or intangible goods, services, or assets for money
Finance	earnings before interest	measure of a firm's profit that includes all incomes and expenses except interest expenses and income tax expenses
Finance	efficiency ratios	expenses as a percentage of revenue, with a few variations
Production	Sales	transaction between two or more parties that involves the exchange of tangible or intangible goods, services, or assets for money
Production	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.
Billing	Sales	transaction between two or more parties that involves the exchange of tangible or intangible goods, services, or assets for money
Billing	Monthly Recurring Revenue	the predictable total revenue generated by your business from all the active subscriptions in a particular month
Billing	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

[Anonymous]

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

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.

[dwel0120]

That will not work because definitions change all the time.