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...
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]
)

     

grantsamborn's avatar
grantsamborn
Icon for Solution Sage rankSolution Sage
3 years ago

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'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