Forum Discussion

poweruser55's avatar
poweruser55
Icon for Helper IV rankHelper IV
4 years ago

AND/OR Statements in DAX

[ Spoiler ] There are two calcualations I need to add together in a new column. Above is what I came up with but I got many errors. I think it's mostly just paranthesis issues. I also got ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

poweruser55 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

poweruser55's avatar
poweruser55
Icon for Helper IV rankHelper IV
4 years ago

Var ghosts =
CALCULATE (
COUNT ( '999_Funnel'[Sale Type] ),
FILTER (
'999_Funnel',
'999_Funnel'[Sale Type] = "Assay"
&& '999_Funnel'[_C_Annual Recurring Funnel Adj. (GC)] < 50)||
'999_Funnel',
'999_Funnel'[Sale Type] = "Assay"
&& '999_Funnel'[FV Units] > 0 ||
'999_Funnel',
'999_Funnel'[Sale Type] = "Assay"
&& '999_Funnel'[Capital Funnel Value (GC)] > 0) +
CALCULATE (
COUNT ( '999_Funnel'[Sale Type] ),
FILTER (
'999_Funnel',
'999_Funnel'[Sale Type] = "Equipment" && '999_Funnel'[FV Units] = 0 )||
('999_Funnel'[Sale Type] = "Equipment" && '999_Funnel'[Capital Funnel Value (GC)] = 0 && ('999_Funnel'[Sale Type] = "Equipment" && '999_Funnel'[Finance Type] = "Cash/Lease")|| ('999_Funnel'[Sale Type] = "Equipment" && '999_Funnel'[Monthly Recurring Funnel Value (GC)] = 0 && '999_Funnel'[Finance Type] = "Seed/Rental" && NOT('999_Funnel'[Product.Sales Product Line] = "Automation" || "Atellica Solution Components" || "IT"))))

 

There needs to be two statements added together. The first 

Sale Type ="Assay" &&  (Recurring funnel is <50 EUR || FV unit > 0 || Capital funnel is > 0)

The second is Sale Type = "Equipment" && (FV units = 0 || (Capital funnel = 0 && Finance Type = "Cash/Lease") || (Recurring funnel = 0 && Finance Type = "Seed/Rental" && (NOT Automation || Atellica Solution components || IT)

 

I need that ^ in Power BI terms the picture I posted also explains it in words. I tried my hand at it but I know I am missing some parts Greg_Deckler 

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, poweruser55 

    I didn't find any paranthesis issues, can you share detailed error message?

     

    Best Regards,
    Community Support Team _ Eason