topic Re: DAX not giving the right results with SUMX in DAX Commands and Tips

DAX not giving the right results with SUMX

deepak_d03 — Fri, 21 Jun 2024 20:52:59 GMT

I have the car crash information for the last few years (2015 -2023). I am trying to get the count of car crashes against each year - month combination. I created a Calendar table and connected it with my master data table. (Calendar date and Crash date). I have the below DAX formula for the crash count.

Crash Count =

var FirstDayOfMonth = MIN(Traffic_Crashes[CRASH_DATE])

VAR LastDayOfMonth = MAX(Traffic_Crashes[CRASH_DATE])

RETURN

CALCULATE(SUMX(Traffic_Crashes,

IF(Traffic_Crashes[CRASH_DATE] >= FirstDayOfMonth &&

Traffic_Crashes[CRASH_DATE] <= LastDayOfMonth, 1)))

But the metrix that I have does not give the expected result. Any thoughts?

Re: DAX not giving the right results with SUMX

Greg_Deckler — Fri, 21 Jun 2024 21:13:39 GMT

deepak_d03 That's a weird way of doing that,try either:

Crash Count = var FirstDayOfMonth = MIN(Traffic_Crashes[CRASH_DATE]) VAR LastDayOfMonth = MAX(Traffic_Crashes[CRASH_DATE]) RETURN CALCULATE( COUNTROWS('Traffic_Crashes'), Traffic_Crashes[CRASH_DATE] >= FirstDayOfMonth && Traffic_Crashes[CRASH_DATE] <= LastDayOfMonth )

Crash Count = var FirstDayOfMonth = MIN(Traffic_Crashes[CRASH_DATE]) VAR LastDayOfMonth = MAX(Traffic_Crashes[CRASH_DATE]) VAR __Table = FILTER( 'Traffic_Crashes', [CRASHDATE] >= FirstDayOfMonth && [CRASH_DATE] <= LastDayOfMonth ) VAR __Result = COUNTROWS( __Table ) RETURN __Result

Re: DAX not giving the right results with SUMX

deepak_d03 — Fri, 21 Jun 2024 23:01:54 GMT

Greg_Deckler - getting the same result.

Re: DAX not giving the right results with SUMX

deepak_d03 — Sat, 22 Jun 2024 13:30:26 GMT

tamerj1 - can you check on this one?

DAX for Calendar table -

Calendar = ADDCOLUMNS(

CALENDARAUTO(),

"Year", YEAR([Date]),

"Month", FORMAT([Date], "mmmm"),

"Day of week", FORMAT([Date], "dddd"),

"Month Number", MONTH([Date])

)

Date and Crash_Date are connected.

The data type is the same for both which is date.

Re: DAX not giving the right results with SUMX

Greg_Deckler — Sun, 23 Jun 2024 14:45:09 GMT

deepak_d03 Going to need to share some sample data then and expected results. 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.

Re: DAX not giving the right results with SUMX

tamerj1 — Sun, 23 Jun 2024 19:42:19 GMT

Hi deepak_d03

How about

Crash Count = COUNTROWS ( Traffic_Crashes )

Re: DAX not giving the right results with SUMX

deepak_d03 — Sun, 23 Jun 2024 22:54:12 GMT

The result is the same. Not getting the correct one.

CRASH COUNT NEW is the one with countrows(Crash_Date).

Has it to do anything with the way the Calendar table is created and it connected to the master table? I have posted the DAX formula for Calendar table and the data model. Posting here a screenshot of -the crash data table and Calendar table too. CC - Greg_Deckler =- Please see the screenshots for the sample data and the expected results.