Forum Discussion

VendettaBob's avatar
VendettaBob
Helper II
7 years ago
Solved

DAX Filter Context

Hi,   I have a measure called _TotalUnits which works great.    What I want to do is to work out the sum of DepreciationMonth from another table where the _TotalUnits > 0.   The difficulty is t...
Need Help
  • jtownsend21's avatar
    jtownsend21
    7 years ago

    Yes, your formula is probably correct. Since I couldn't see your data I wasn't sure if there was another field that should be there. 

    HASONEVALUE - To answer your question, I believe the the HASONEVALUE is the true/false condition that tells it when to use which behavior, so at the row level it will use the first part because it is at the row level and therefore 'has one value'. 

     

    VALUES - For the Values function, this just creates a summary table with one column for the SUMX function to use. 

jtownsend21's avatar
jtownsend21
Responsive Resident
7 years ago

Could you share some sample data or what it is doing compared to the desired outcome? 

  • VendettaBob's avatar
    VendettaBob
    Helper II
    7 years ago

    Hi,

     

    So here is some data:

     

     

    Where there is a Total Hire > 0 then I want the _Test to be displayed.

     

    My formula is:

    _Test = CALCULATE(SUM(IndividualItem[DepreciationMonth]), FILTER(FleetSize,[_TotalUnits]>0))
     
    _TotalUnits: CALCULATE(SUM(FleetSize[Unit]), FILTER(ALL(YearMonth[DateValue]),YearMonth[DateValue] <= MAX(YearMonth[DateValue])))
     
    Any idea why it's not working or can you suggest an alternative?
     
    Thanks
    • VendettaBob's avatar
      VendettaBob
      Helper II
      7 years ago

      IF I use this formula: 

      _Test = IF([_TotalUnits]>0,SUM(IndividualItem[DepreciationMonth]),BLANK())
       
      THe row level looks correct but not the total
       
      • jtownsend21's avatar
        jtownsend21
        Responsive Resident
        7 years ago

        Try creating a second measure. 

        _Test Total = 
IF(
    HASONEVALUE(
        IndividualItem[DepreciationMonth]),
        [_Test],
    SUMX(
        VALUES(IndividualItem[DepreciationMonth]),
        [_Test]
    )
)