Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VendettaBob
Helper II
Helper II

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

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. 

View solution in original post

9 REPLIES 9
jtownsend21
Responsive Resident
Responsive Resident

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

Hi,

 

So here is some data:

 

Capture.GIF

 

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

IF I use this formula: 

_Test = IF([_TotalUnits]>0,SUM(IndividualItem[DepreciationMonth]),BLANK())
 
THe row level looks correct but not the total
 
Capture.GIF

Try creating a second measure. 

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

That hasn't worked:

 

Capture.GIF

 

The total is still incorrect....

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

 

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. 

@VendettaBob please read following post to get your answer quickly.

 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

VendettaBob
Helper II
Helper II

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

 

OnHire     DepreciationMonth

1

0

0

0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.