A trickey DAX formula for fundraising

[npatten]

Hi-- I'm a prospect manager/data analyst with the children's hospital in Vancouver, BC. I'm the only one I know in my organization working with DAX.  I'm trying to simplify a SQL formula to work in DAX that returns a specific monetary value from one of three columns based on the proposal status of another column. Basically donor proposals can have different values based on the fundraising cultivation stage. The IT colleauge developed the formula in SQL to run a Crystal Report, but I'm trying to adapt the formula into the Power Pivot/Power BI space. I believe the formula is set up as a series of IF statements, and it works in PBI, but not in 2010 Power Pivot. Here's the formula:

if([Prospect Proposal Stage]="11. MG Qualification") or ([Prospect Proposal Stage]="12. MG Early Cultivation") or ([Prospect Proposal Stage]="13. MG Advanced Cultivation") or ([Prospect Proposal Stage]="14. MG Solicitation in Progress") or ([Prospect Proposal Stage]="17. Payment Pending") then [Prospect Proposal Initial Target Amount] else if([Prospect Proposal Stage]="15. MG Decision Pending") or ([Prospect Proposal Stage]="16. MG Verbal Commitment") then [Prospect Proposal Amount Asked] else if ([Prospect Proposal Stage]="6. Confirmed") then [Prospect Proposal Amount Funded] else 0

For some reason, this won't work in PowerPivot. forAnyone have any idea of what's going on? I can post some sample data if needed. Thanks in advance for any help.

[Sean · Accepted solution]

@npatten 

You are in the Query Editor => M (that's you original formula)

To use the DAX formula => close the Query Editor => go to the Appropriate Table => click New Column in the Modeling Tab

[Sean]

@npatten for starters this is M not DAX syntax

I have not reviewed the formula itself but if you say its working in PBI (should be fine)

M - Power Query (Query Editor)

and

DAX - Power Pivot (PBI)

[npatten]

Thank you Sean, any idea how I would turn it into a DAX formula? I only know of M from what I've read on the msdn pages, not how to write with it.

[Sean]

2010 Power Pivot??? You are using the original PowerPivot? when it was actually 1 word? I'm lost - why?

[npatten]

Excel 2010, and the associated Power Pivot add on for that version, is the only version we're allowed to have installed on our workstations. I'm going to have to figure out a way to start using my personal computer for work needs becuase our systems our so outdated. We're still using Windows 7, for example. My understanding though is that there a some limitations with the 2010 software in it's ability to use the full extent of Power Pivot. However, I have the most current version of Excel on my personal computer. I'll mostly likely have to upload PBI data sets to the service, then download them on my personal computer and use excel 2016. Workarounds....

[Sean]

This should be your DAX equivalent... Let me know if it does the job?

Formula =
IF (
    [Prospect Proposal Stage] = "11. MG Qualification"
        || [Prospect Proposal Stage] = "12. MG Early Cultivation"
        || [Prospect Proposal Stage] = "13. MG Advanced Cultivation"
        || [Prospect Proposal Stage] = "14. MG Solicitation in Progress"
        || [Prospect Proposal Stage] = "17. Payment Pending",
    [Prospect Proposal Initial Target Amount],
    IF (
        [Prospect Proposal Stage] = "15. MG Decision Pending"
            || [Prospect Proposal Stage] = "16. MG Verbal Commitment",
        [Prospect Proposal Amount Asked],
        IF (
            [Prospect Proposal Stage] = "6. Confirmed",
            [Prospect Proposal Amount Funded],
            0
        )
    )
)

[npatten]

Thank you! When I plug it into PBI it comes back with an error 'Token Comma expected' at the first |. If I understand it, the | | is the OR operator? I've included some sample date for you if you would like to play around with it. Again, thank you for you time.

Prospect Proposal Initial Target Amount	Prospect Proposal Amount Asked	Prospect Proposal Amount Funded	Prospect Proposal Stage
600000	600000	600000	17. Payment Pending
300000	250000	250000	16. MG Verbal Commitment
250000	240266	240268	17. Payment Pending
50000	0	50000	17. Payment Pending
20000	20000	6500	17. Payment Pending
100000	0	0	11. MG Qualification
25000	0	0	12. MG Early Cultivation
50000	0	0	13. MG Advanced Cultivation
2000000	0	0	14. MG Solicitation in Progress
100000	100000	0	15. MG Decision Pending
100000	100000	0	16. MG Verbal Commitment

[kcantor]

I would also suggest checking the directions of your relationships. PowerBI seems to work better with relationships for me. You may need to change the direction in your powerPivot.

Of course you could take your PowerBI build and export the data into Excel as a last ditch effort.

[npatten]

Hi--- Thanks for responding. i don't have the table related to any other tables. It functions as a standalone data source. Is cardinality still an issue? Also, when I go to the export function in PBI it only gives me the 'publish option'. Is there another way to get the PBI doc into excel? I'm bound to excel 2010 tho.

[Sean]

@npatten Can you post a picture of where you are getting the error?

It works with the sample you provided...

[npatten]

[Sean · Accepted solution]

@npatten 

You are in the Query Editor => M (that's you original formula)

To use the DAX formula => close the Query Editor => go to the Appropriate Table => click New Column in the Modeling Tab

[Sean]

@npatten in the Query Editor you enter the original M formula

and as you said it does work...

[npatten]

I was able to get your DAX formula to work! I edited it to include the table references in addition to the column references, eg ( vwMG_ActiveProposals[Prospect Proposal Stage] = "11. MG Qualification"). If I understand the issues correctly: 1) DAX formulas are entered into via the Data Modeling tab using the formula editing bar, whereas M formulas are entered via the query editor; 2) i had to add the table references in addition to the column headings in DAX to reference the correct data locations. There are other tables in the PBI doc with the same column headings, generating some calculation confusion. Do these sound right?

Also, i tried it in 2010 power pivot and it works too! Many, many, thanks 🙂

[Sean]

Yes for Columns you reference the table => TableName[Column] and for Measures you do not just => [Measure]

And yes in the Query Editor we use M!

[Sean · Accepted solution]

@npatten The formula seems fine and working.

Discounted Amount Column =
IF (
    vwMG_ActiveProposals[Prospect Proposal Probability] = "Possible",
    vwMG_ActiveProposals[Stage Amount 2] * .3,
    IF (
        vwMG_ActiveProposals[Prospect Proposal Probability] = "Probable",
        vwMG_ActiveProposals[Stage Amount 2],
        0
    )
)

Seems to works as intended...

[npatten]

Hello-- Thank you so much! My original attempt had an unneeded ) the .3. This is awesome. I think I'm startint to understand the DAX logic a bit better. Thank you again 🙂