DAX query showing a different total number

[mroomi]

Hi,

I am trying to calculate the number of students and when I run SQL it gives me a different number then what I see on a Line bar visual.

I grabbed the SQL using query profiler which gave me the following DAX query:

EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
KEEPFILTERS(
SAMPLE(
3502,
FILTER(
KEEPFILTERS(VALUES('StudentAttendanceData'[AttendanceDate])),
OR(
NOT(
ISBLANK(CALCULATE(SUM('StudentAttendanceData'[All Enrolled])))
),
NOT(
ISBLANK(CALCULATE(SUM('StudentAttendanceData'[Present Students])))
)
)
),
'StudentAttendanceData'[AttendanceDate],
1
)
),
"SumAll_Enrolled", CALCULATE(SUM('StudentAttendanceData'[All Enrolled])),
"SumPresent_Students", CALCULATE(SUM('StudentAttendanceData'[Present Students]))
),
KEEPFILTERS(
FILTER(
KEEPFILTERS(VALUES('StudentAttendanceData'[AttendanceDate])),
AND(
'StudentAttendanceData'[AttendanceDate] >= DATE(2016, 10, 10) ,
'StudentAttendanceData'[AttendanceDate] < (DATE(2016, 10, 10) + (TIME(23, 59, 59) ))
)
)
)
)

ORDER BY
'StudentAttendanceData'[AttendanceDate]

SQL Query:

Present Students 161

Enrolled Student 278 

VS

DAX

Present Students 177

Enrolled Student 315

[mroomi]

Hi need HELP!!

When I execute a command in Tabular model I get the follwoing numbers

Sum of present Studnets  161 

Sum of all enrolled 278

I execute the DAX formula ( i grabbed it using the profiler ) it shows me 

Sum of present Studnets  171 

Sum of all enrolled 315

My DAX formula is:

EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
KEEPFILTERS(
SAMPLE(
3502,
FILTER(
KEEPFILTERS(VALUES('StudentAttendanceData'[AttendanceDate])),
OR(
NOT(
ISBLANK(CALCULATE(SUM('StudentAttendanceData'[All Enrolled])))
),
NOT(
ISBLANK(CALCULATE(SUM('StudentAttendanceData'[Present Students])))
)
)
),
'StudentAttendanceData'[AttendanceDate],
1
)
),
"SumAll_Enrolled", CALCULATE(SUM('StudentAttendanceData'[All Enrolled])),
"SumPresent_Students", CALCULATE(SUM('StudentAttendanceData'[Present Students]))
),

KEEPFILTERS(
FILTER(
KEEPFILTERS(VALUES('StudentAttendanceData'[AttendanceDate])),
AND(
'StudentAttendanceData'[AttendanceDate] >= DATE(2016, 10, 10),
'StudentAttendanceData'[AttendanceDate] < DATE(2016, 10, 11)
)
)
)
)

ORDER BY
'StudentAttendanceData'[AttendanceDate]

Any help will be gretaly appreciated.

Thanks,

Roomi

[WP]

Hi Mroomi,
What do you want to calculate, which formula are you using to do this and how does the model looks like?
I believe the profiler output is internal translation of your own formula, so I think it s better to start from actual formula and goals.

[mroomi]

Hi WP,

I checked the numbers in Model and just did a simple sum and I got a number which is completely different when I used a PowerBI visual.

[mroomi · Accepted solution]

Resolved, by changing the property of the model project from DEFAULT to FULL processing. Not sure why it was shoing different total.