topic Re: How to display Grand Total correctly with USERELATIONSHIP? in DAX Commands and Tips

How to display Grand Total correctly with USERELATIONSHIP?

Anonymous — Tue, 13 Jun 2023 15:01:09 GMT

Good day,

I’m having trouble with getting the correct Grand Total for my pivot table.

I have two tables.

Fact table of email worked by an employee. It has a pkey column which is just a concatenation of week and agentId.
Dimension table that has the login data of employee. It has an active citrixKey connected to Fact table.In general, Fact.agentId is the same as Dimension.citrixUsername, however an employee encountered issues with the citrixUsername and is now using Dimension.ntLogin. When I tried to build a pivot table with rows as agentId and values as sum of values, the grand total is not correct.

Here are my measures:
sumEmails:=
SUM( 'Fact'[Value] )

calcEmails:=
IF(
[sumEmails] = BLANK(),
CALCULATE(
[sumEmails],
USERELATIONSHIP('Fact'[pkey], 'Dimension'[ntloginKey])
),
[sumEmails]
)

Sample

Re: How to display Grand Total correctly with USERELATIONSHIP?

tamerj1 — Tue, 13 Jun 2023 15:12:47 GMT

Hi Anonymous

Please try

calcEmails :=
SUMX (
VALUES ( 'Fact'[Fact.agentId] ),
COALESCE (
[sumEmails],
CALCULATE (
[sumEmails],
USERELATIONSHIP ( 'Fact'[pkey], 'Dimension'[ntloginKey] )
)
)
)

Re: How to display Grand Total correctly with USERELATIONSHIP?

Anonymous — Tue, 13 Jun 2023 17:50:26 GMT

hi tamerj1 , thanks for the input. i am using excel. and it seems coalesce is not available.

Failed to resolve name 'COALESCE'. It is not a valid table, variable, or function name.

Re: How to display Grand Total correctly with USERELATIONSHIP?

tamerj1 — Tue, 13 Jun 2023 17:53:26 GMT

Anonymous

Then try

calcEmails :=
SUMX (
VALUES ( 'Fact'[Fact.agentId] ),
VAR SumEmails = [sumEmails]
RETURN
IF (
ISBLANK ( sumEmails ),
CALCULATE (
[sumEmails],
USERELATIONSHIP ( 'Fact'[pkey], 'Dimension'[ntloginKey] )
),
sumEmails
)
)

Re: How to display Grand Total correctly with USERELATIONSHIP?

Anonymous — Tue, 13 Jun 2023 18:05:51 GMT

tamerj1 , i tried the revised measure. however, it only shows the data for week 6/4/23. it does not show the data of ceb1ccabalquinto(ntloginKey) on week 6/11/23.

Re: How to display Grand Total correctly with USERELATIONSHIP?

tamerj1 — Tue, 13 Jun 2023 19:02:58 GMT

Anonymous

You may try something complex like

calcEmails :=
SUMX (
DISTINCT (
UNION (
VALUES ( 'Fact'[Fact.agentId] ),
CALCULATE (
VALUES ( 'Fact'[Fact.agentId] ),
USERELATIONSHIP ( 'Fact'[pkey], 'Dimension'[ntloginKey] )
)
)
),
VAR SumEmails = [sumEmails]
RETURN
IF (
ISBLANK ( sumEmails ),
CALCULATE (
[sumEmails],
USERELATIONSHIP ( 'Fact'[pkey], 'Dimension'[ntloginKey] )
),
sumEmails
)
)

or could something just simple as

calcEmails :=
CALCULATE (
[sumEmails],
USERELATIONSHIP ( 'Fact'[pkey], 'Dimension'[ntloginKey] )
)
+ [sumEmails]