Incorrect Data model

[bergen288]

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 User_ID has 1:* relationship with UserID in fact table.  Both User_ID and UserID are whole number integers.

Below is my target table visual with all columns from fact table.  Everything is good so far.

My goal is to add Assignee in dimentional table.  To show the issue here, UserID in dimentional table is added.  As you can see below,  one row in fact table matches all rows in dimentional table.  Looks like my data model doesn't work at all.

I tried to follow the troubleshooting steps mentioned here: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-troubleshoot,  but I didn't see any obvious issue.  Any suggestion for troubleshooting?

Thanks.

[bergen288]

Sorry, I made a mistake to say SUMX function worked.  I don't want to cause any confusion here.  SUMX function has the same issue with SUM function.  My workaround is to define 2 different measures with and without IF function and use them accordingly.

[bergen288]

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.

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work

=SUMX(Calendar,[Session shours])

[bergen288]

Yes, the following measure with SUMX function works in both table visual and area chart visual:

Session Hours = SUMX('PBI_XZ_Case_Time_Session',[Session Seconds]) / 3600

Thanks a lot.

[Ashish_Mathur]

You are welcome.

[sevenhills]

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
)

[bergen288]

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.  

[sevenhills]

I guess we may have to use SUMX in place of SUM. 

Which is posted by another user below.