Matrix expand down yields different results for expand all vs expanding one row

[Anonymous]

My measure counts FTEs (full-time equivalents) as the result of dividing a resource's working hours capacity per day into the base calendar's working hours capacity per day. My FTE measure is:

FTE =

     IF(

          NOT(

          ISFILTERED(

               Projects[ProjectName])),

               CALCULATE(

               SUMX (

                    VALUES ( Resources[ResourceName] ),

                    CALCULATE(

                         DIVIDE(

                              SUM(ResourceTimephasedDataSet[Capacity]),

                              SUM(ResourceTimephasedDataSet[BaseCapacity])

                          )

                    )

               )

               )

)

ResourceTimephasedDataSet looks like:

Comment	Date	ResourceId	Capacity	BaseCapacity
Weekend	30-Aug-2020	1	0	0
Working Day	31-Aug-2020	1	8	8
Working Day	1-Sep-2020	1	8	8
Working Day	2-Sep-2020	1	8	8
Working Day	3-Sep-2020	1	8	8
This resource works Friday morning only	4-Sep-2020	1	4	8
Weekend	5-Sep-2020	1	0	0
Weekend	6-Sep-2020	1	0	0
Holiday in US	7-Sep-2020	1	0	0
Working day	8-Sep-2020	1	8	8

When I expand down the entire level, the matrix shows the expected values for each resource and team. The rows are Team > Resource > Project. FTE is not meaningful at the project level so that level always shows a blank.

If I expand down just one team, the matrix shows the total for the team at each individual resource level. It should show each resource's FTE count.

This solution has the same results whether I have days, weeks, or months, in my columns - which is what I need.

[Greg_Deckler]

@Anonymous - Difficult to reproduce based upon the data provided. I'm a bit concerned about your use of VALUES but otherwise hard to see anything specific looking at the code. I'm not a fan of VALUES. Wait a minute, that sounded bad...

Anyway, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Thanks @Greg_Deckler - I'll look at those resources. I used VALUES because I saw it in a similar type of solution on (I think) the Italians' website. The crux of the problem is that I get different results if I expand the matrix down one level using the split arrow (1st image below) vs clicking on the plus sign to expand one of the rows in my hierarchy (2nd image). Note that expanding down one more level is the "ProjectName" level in my DAX measure and correctly shows blanks as this measure is not meaningful at that level.

[Anonymous · Accepted solution]

@Greg_Deckler , your MM3TR&R came somewhat close to my solution but, in the end, it was a combination of you writing you're not fond of VALUES and learning about VALUES and DISTINCT in the SQLBI DAX video course that might have solved the issue. I replaced VALUES with DISTINCT and I'm getting the results I expect, no matter which way I expand my matrix. Thanks for the push in the right direction!

[Greg_Deckler]

@Anonymous Awesome! Please mark a post as a solution so others can benefit. Yeah, I used to use VALUES for similar reasons and then I decided that VALUES is not a good function in many cases and really isn't needed when there are other ways to do the same thing. Remember though, DAX has evolved greatly over the years so a lot of old posts do things in a way that you probably wouldn't do today. Also, everyone's understanding of DAX is improved over the years. I look back on a lot of my old DAX and think "Yeeeesh!!". 😕