Totals Issue with USERELATIONSHIP DAX Function

Hello All,

I have the Fact Table Data in the below format.

image

I created a Date Table
Date Table = calendar(DATE(2020,04,01),DATE(2023,12,31))

And Relationship is like
Active Relationship between Fact[Mat Date] - Date Table[Date]
InActive Relationship between Fact[Avail Date]- Date Table[Date]

My scenario is

I have Start Date (01-04-2022) and End Date(30-09-2022) as date Filter from Date Table and
I will be getting 4 rows with my selection, to get Yellow Highlighted row(5 th row)

I created a measure using the DAX
Availed Date =
var a = CALCULATE(MAX(Fact[Avail Date]),USERELATIONSHIP(Fact[Avail Date],Date Table[Date]))
var b = MAX(Fact[Avail Date])
return
IF(ISBLANK(a),b,a)
I’m getting correct result with the above DAX.

The same logic for Amt
Amt =
var a = CALCULATE(SUM(Fact[Amt]),USERELATIONSHIP(Fact[Avail Date],Date Table[Date]))
var b = SUM(Fact[Amt])
return
IF(ISBLANK(a),b,a)
I should get the Total 40833.6 but I’m getting 29,166.69 (Total- It’s excluding the first row i.e, 01-03-2022).
SUMX = SUMX(VALUES(Dim[ID]),[Amt]) This is giving 35000.06 as Total but Last row is Blank.

Here’s the Link for the pbix File

Wrong Totals with Userelationship.pbix (60.2 KB)

@Harsh

Can anyone suggest the alternative for this!!!
TIA

Bumping this post for more visibility from our experts and users.

Hi @likhitha - Try this measure

Total Amount = var Avail = CALCULATETABLE(sheet2,USERELATIONSHIP(‘Date Table’[Date],Sheet2[Avail Date]))

var Mat = CALCULATETABLE(Sheet2)

return

sumx(DISTINCT(union(Avail,Mat)),Sheet2[Amt])

Attached PBIX
Wrong Totals with Userelationship.pbix (60.3 KB)

Thanks
Ankit J

Hi Ankit…
It worked out Perfectly.Thank you so much for the response :slight_smile:
I got the solution with the below approach.But yours pretty straight forward.
Flag =

var a = MAX(‘Date Table’[Date])

var b = CALCULATE(MAX(Sheet2[Mat Date]),USERELATIONSHIP(‘Date Table’[Date],Sheet2[Avail Date]))

return

IF(b>a,1,0)

2.Derived Amt for the Flag output if it’s 1.

Amt_Last Row =

var a = MAX(‘Date Table’[Date])

var d = CALCULATE(SUM(Sheet2[Amt]),USERELATIONSHIP(‘Date Table’[Date],Sheet2[Avail Date]),Sheet2[Mat Date]>=a)

return

IF([Flag]=1,d)

3.Then Added the Amt from Fact Table with Step 2 Result.

Amt_Result =

var a = [Amt_Last Row]

var b = SUM(Sheet2[Amt])

return

a+b

Thanks @likhitha - can you please mark this as solved.