Forum Discussion
Solved
Incorrect Data model
I am puzzled with my incorrect data model and any help is really appreciated. In the screenshot below, PBI_XZ_Case_Time_Session is my fact table and PBI_XZ_Case_Fiserv is my dimentional table whose ...
Hi,
Does this measure work
=SUMX(Calendar,[Session shours])
Actually, "Session Hours" is a measure as below.
Session Hours =
VAR __HOURS = SUM( 'PBI_XZ_Case_Time_Session'[Session Seconds] ) / 3600
RETURN IF(__HOURS >0, __HOURS, 0)
For unknown reason, its IF statement is the root cause. The issue is fixed with the following measure.
Session Hours = SUM( 'PBI_XZ_Case_Time_Session'[Session Seconds] ) / 3600
I still need a measure with IF statement in order to plot session hours correctly. For example, below are the screenshots with correct session hours with IF statement on left and incorrect session hours without IF statement on right. Surely I can define the 2nd Session Hour with IF statement. But I would like to know why IF statement causes the issue here and the best way to handle my case.
Thanks.
Can you try this?
Session Hours =
VAR __HOURS = SUM( 'PBI_XZ_Case_Time_Session'[Session Seconds] ) / 3600
RETURN IF(ISBLANK(__HOURS) || __HOURS <= 0, 0, __HOURS)
or
Session Hours =
RETURN IF(
DIVIDE( SUM( 'PBI_XZ_Case_Time_Session'[Session Seconds] ), 3600, 0) > 0
, DIVIDE( SUM( 'PBI_XZ_Case_Time_Session'[Session Seconds] ), 3600, 0)
, 0
)
I tried both but none worked. As I said, the root cause is the IF statement. I also tried SWITCH statement and found it has the same issue.
I guess we may have to use SUMX in place of SUM.
Which is posted by another user below.