Forum Discussion

likhithar's avatar
likhithar
Helper III
3 years ago
Solved

Totals Issue with USERELATIONSHIP DAX Function

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

Tips and Tricks
Tutorial Requests
  • likhithar's avatar
    likhithar
    3 years ago

    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
     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

     

     

     

     

  • grantsamborn's avatar
    grantsamborn
    Solution Sage
    3 years ago

    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's avatar
      likhithar
      Helper III
      3 years ago

      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)

  • likhithar's avatar
    likhithar
    Helper III
    3 years ago

    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