DAX - If statement using measures returning too many rows

[WBscooby]

Hi

I'm a bit rusty on DAX and hoping someone can help.

I have a table of customer outcomes containing Outcome ID, Outcome Date, Customer ID

The table links to DateCalendar and the Customer Table.

I use a slicer to filter outcomes by date and display this in a table alongside the date of the outcome and Customer ID.

I have then created a measure to calculate the Earliest outcome date for each customer for all of their outcomes and another measure to show the date of outcome (only for calculation purposes).

Earliest Outcome Date = CALCULATE(
Min(OUTCOMES[Date_Outcome]),
ALL(OUTCOMES),
VALUES(OUTCOMES[Customer_ID])
)

Date_outcome = CALCULATE(
min(OUTCOMES[Date_Outcome]),
ALLSELECTED(OUTCOMES[Customer_ID])
)

Both of these measures calculate correctly when I drop them into the table.

Finally, I created a measure to show if the Earliest outcome date is before the Date of Outcome ie the customer has previous outcomes. I have used the first 2 measures and if statement to do this.

Earlier Outcome Date = IF([Earliest Outcome Date]<[Date_outcome],1,0)

When I drop this into the table, I get a huge number of rows including customers that don't have outcomes. The rows are correct for the values I need to select but I can't understand why the other are there. If I do a simple datediff instead, this seems to work but is not what I'm trying to achieve.

Can anyone help me to understand why this is happening and hopefully resolve it?

Thank you!

[WBscooby]

Hi Grant

Sorry, not very well worded!

Some of the customers have more than one outcome in a period.

I'm trying to create a table that has shows customers with an outcome in the period. I then want to show if they have previous outcomes. Rather than sum the outcomes, it would be a simple 1 for yes they had a pervious outcome or 0 if not.

I've put a slider in your pbi file to narrow down the results to try to demonstrate. Cust C has a 2 for zmeasure, but I need this to be a 1 so I can count the number of customers  in the period that had a previous outcome.

Hope that makes more sense! Thank youhttps://westberksgovuk-my.sharepoint.com/:u:/r/personal/jessica_clark_westberks_gov_uk/Documents/Cus... 

[grantsamborn]

Hi @WBscooby 

The link at the end of your post is asking me for a password.

[WBscooby]

Sorry, think it's the my organisational security. Trying from another location!

CustomerOutcomesV2.pbix

[grantsamborn · Accepted solution]

@WBscooby 

How about this?

zMeasure 2 = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZECOLUMNS(
            'Customer'[Customer],
            'Date'[MMM-YY]
        ),
    "@Value",
        IF( [Earliest Outcome Date] < [Date_outcome], 1 )
    ),
    [@Value]
)

[WBscooby]

Actually, when I mess about with the columns I drop in, it is working! Thank you so much for your help!

[WBscooby]

Thanks for looking - I've tried but it is still not working for me

[grantsamborn]

Hi @WBscooby 

Can you explain wht is not working?

Here is my latest pbix: CustomerOutcomesV2-mine.pbix

Grant

[grantsamborn]

Hi @WBscooby 

Instead of your [Earlier Outcome Date], try this:

zMeasure = 
SUMX(
    'Outcomes',
    IF( [Earliest Outcome Date] < [Date_outcome], 1, 0 )
)

pbix: CustomerOutcomes.pbix

[WBscooby]

Brilliant, this works, thank you! Just one more question if you don't mind. Some customers have more than one outcome so have a result of 2. Can this be tweaked to only show 1 by customer ie more like earlier outcome-  Yes/ No?

[grantsamborn]

Hi @WBscooby 

I'm not sure I understand. 

Do you want 1 line per customer with a count off outcomes between [Earliest Outcome Date] and [Date_outcome]?

It might help to try to reword your request.  Let me know.

Grant