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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
v-rongtiep-msft
Community Support
Community Support

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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