cancel
Showing results for
Did you mean:
Helper I

## 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 the filter/row context. I want this to work at the leaf level (lowest level rather than an aggregated level.

I was trying somthing like:

```_Months =
VAR filterIndividualItem =
FILTER(FleetSize,[_TotalUnits]>0)
RETURN
CALCULATE(
SUM(IndividualItem[DepreciationMonth])
,filterIndividualItem
)```

However, this isn't working.

Any ideas?

Thanks,

Bob

1 ACCEPTED SOLUTION

Accepted Solutions
Responsive Resident

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.

9 REPLIES 9
Helper I

So I don't know why this isn't working:

OnHire     DepreciationMonth

1

0

0

0

Responsive Resident

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

Super User IV

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

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
Helper I

IF I use this formula:

_Test = IF([_TotalUnits]>0,SUM(IndividualItem[DepreciationMonth]),BLANK())

THe row level looks correct but not the total

Responsive Resident

Try creating a second measure.

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

That hasn't worked:

The total is still incorrect....

Helper I

Hi,

So I think this works (replace the months with the Business Key):

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

What is the purpose of the HASONEVALUE and also the VALUES functions?

Thanks

Responsive Resident

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors