Forum Discussion

WBscooby's avatar
WBscooby
Icon for Helper III rankHelper III
3 years ago
Solved

DAX - If statement using measures returning too many rows

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!

Need Help
  • grantsamborn's avatar
    grantsamborn
    3 years ago

    WBscooby 

    How about this?

     

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

     

10 Replies

    • WBscooby's avatar
      WBscooby
      Icon for Helper III rankHelper III
      3 years ago

      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's avatar
        grantsamborn
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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

         

  • WBscooby's avatar
    WBscooby
    Icon for Helper III rankHelper III
    3 years ago

    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/CustomerOutcomesV2.pbix?csf=1&web=1&e=kgWvH2