Totals Issue with USERELATIONSHIP DAX Function

[likhithar]

Hello All,

I have the Fact Table Data in the below format.

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 betweenFact[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 Higlighted 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).

Here's the Link for the pbis File

https://drive.google.com/file/d/14s8Nlak4dU4WW3nKY69VJL03duVRU69E/view?usp=sharing

Can anyone suggest the alternative for this!!!
TIA

[likhithar · Accepted solution]

Hello All,
I got the solution for the issue which I posted above.

1.First I figured out the Mat Date which is greater than End Date(or Max Date in our Slicer Selection)based on Avail Date using USERELATIONSHIP DAX Function and created a Flag for it.

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

Here's my Output

Thanks everyone  for the replies.

Here's the Link to access the pbix File

https://drive.google.com/file/d/15O8292hZhiQcO77LBctlB1AoUv_Qyxii/view?usp=sharing

[grantsamborn]

Hi @likhithar 

I hope I understood this correctly but I'm not sure it has to with [Availed Date].

If your date table is filtered by 2022-04-01 to 2022-09-30, the last row wouldn't be included in the total since the Mat Date is 2022-12-26 --- outside of your filter range.

pbix: likhithar - 1.pbix  (I replaced your date table.)

Let me know if that makes sense.

Grant

[likhithar]

@grantsamborn  the last row should include in the reporting because Avail Date(01-09-2022) is before Max Date from Date Table(30-09-2022)

[Anonymous]

Hi @likhithar ,

Do all the fields of your visual come from the fact table? According to your description, when the slicer is filtered, only data that meets the slicer criteria is displayed. As follows:

If you want to show all totals, you can create a measure to display as a card visual.

Total = CALCULATE(SUM('Fact'[Amt]),FILTER(ALL('Fact'),'Fact'[ID]=MAX('Fact'[ID])))

Or you can delete the slicer and set conditional formatting to highlight the data you don't want to display, so that the total is also correct.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.