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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Multi-step DAX formula - is there a way to simplify?

Hi

I'm quite new to DAX so apologies if this is a simple or stupid question, but I would really appreciate some help as I'm stuck 😞  I'm convined that there has to be an easier way to acheive this calculation.

 

I have an Opportunity table from Salesforce with the column headers (sorry some values are in Spanish):

  • Formato_Oportunidad_c
  • Type
  • Name

I am trying to write a DAX formula that returns a number count for all Opportunities.  At the moment I have achieved the result with three measures, but ideally it would be better to have this as just one.

(I alread have a measure called "Opps Open" which contains the DistinctCount function):

 

I'm looking to return a count of Opportunitities that:

  • Do not contain the words "Venta or "Venta Vasos" in column Formato_Oportunidad__c
  • Do contain the word "Nueva" or "Ampliación" in the column Type

But also counting in the same measure:

  • When column Type contains the word "Renovación" but only at the same time as the Name column includes the word somewhere in the text "Renting"

The three measures I have to achieve this are:

Measure 1:

Opps Open Rental:=CALCULATE([Opps Open],'OpportunityFCT'[Formato_Oportunidad__c]<>"Venta" && 'OpportunityFCT'[Formato_Oportunidad__c]<>"Venta Vasos",'OpportunityFCT'[Type]="Nueva" || 'OpportunityFCT'[Type]="Ampliación")

Measure 2:

Opps Open Rental2:=CALCULATE([Opps Open],'OpportunityFCT'[Formato_Oportunidad__c]<>"Venta" && 'OpportunityFCT'[Formato_Oportunidad__c]<>"Venta Vasos",'OpportunityFCT'[Type]="Renovación",'OpportunityFCT'[Name]="Renting")

 

Measure 3:

Opps Open Total:=[Opps Open Rental]+[Opps Open Rental2]

 

There must be a simpler way than creating 3 measures to get to the result.

Any help or advice would be very much appreciated.

 

Regards

Debbie

4 REPLIES 4
Nishantjain
Continued Contributor
Continued Contributor

@Anonymous  may be something like this

 

Measure 3 :=
CALCULATE (
    [Opps Open],
    'OpportunityFCT'[Type] = "Nueva"
        || 'OpportunityFCT'[Type] = "Ampliación"
        || AND (
            'OpportunityFCT'[Type] = "Renovación",
            'OpportunityFCT'[Name] = "Renting"
        ),
    'OpportunityFCT'[Formato_Oportunidad__c] <> "Venta"
        && 'OpportunityFCT'[Formato_Oportunidad__c] <> "Venta Vasos"
)
Anonymous
Not applicable

Thanks for your responses, much appreciated indeed 🙂

 

I tried:

Measure test:=CALCULATE ([Opps Open],'OpportunityFCT'[Type] = "Nueva" || 'OpportunityFCT'[Type] = "Ampliación" || AND ('OpportunityFCT'[Type] = "Renovación", 'OpportunityFCT'[Name] = "Renting"), 'OpportunityFCT'[Formato_Oportunidad__c] <> "Venta" && 'OpportunityFCT'[Formato_Oportunidad__c] <> "Venta Vasos" )

 

But I got an error saying "The expression contains multiple columns, but only a single column can be used in a true/false expression that is used as a table filter expression"

 

Any idea how to fix it?

parry2k
Super User
Super User

@Anonymous may be something like this

 

Opps Open Rental:=CALCULATE(
[Opps Open],'
OpportunityFCT'[Formato_Oportunidad__c]<>"Venta" && 'OpportunityFCT'[Formato_Oportunidad__c]<>"Venta Vasos",
'OpportunityFCT'[Type] IN  {"Nueva" , "Ampliación", "Renovación","Renting"})


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi Parry2k, thanks so much for your reply.

 

The formula you suggested returned a matching figure (happy days), but I was hoping I could understand more about how it works please.

 

I only need the word "Renting" in the Opportunity Name to only be included in the calculation when the Type = "Renovación", but not for Opportunity Types = "Nueva" or "Ampliación".  Is that what the formula below is doing?

 

Do you know what I'd need to do to also say when the words in the Opportunity Name are either "Renting" or "Rentig"? 

(I found some with typos so wanted to include those too)

Any help is very much appreciated - thank you so much.

Debbie

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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