Measure causes strange behavior in Table Visual

Anonymous — Thu, 07 May 2020 15:11:04 GMT

I have a model with three related tables that I'm attempting to build some measures off of.

One table represents the existing breakdown of program area for a site. So what the client "has". The other table represents program area the client might add to the sites in the future. I have a calendar table that they both have a relationship to. There are look-up tables for classification of the area(s) per site, so there are mutliple rows per site (in each table).

I have a measure that calculates thetotal of the Existing Program Area:

Existing Conditions Total = 

CALCULATE(

    SUM(prog_SiteProgramExisting[Program Area]),

    VALUES(site_Sites[Abbreviated Name]),

 ALL('Date'[Date])

)

I have another measure that calculates the running total of the Proposed Program Area and adds the total from the above measure, so that you should have cumlative total of both the existing and what has been added (or subtracted):

Site Program Proposed Gross Running Total = 

CALCULATE(

    SUM(prog_SiteProgramStages[Program Area Gross]),

    FILTER(

     ALL(prog_SiteProgramStages),

 prog_SiteProgramStages[Date Program Available on Site] <= MAX(prog_SiteProgramStages[Date Program Available on Site])

 ),

 VALUES(site_Sites[Abbreviated Name])

) + [Existing Conditions Total]

All the numbers calculate correctly. Where it gets weird is when I add the second measure to table that is supposed to show the rows of "new work". Here is a pic of what I expect the table to look like:

However, when I add the measure that should give me the existing + new, I get a ton of extra rows, that are non-sensical to me. I assume it has to do with all the "Existing" row data, but I was suprised that adding a measure would cause those rows to propegate.

The numbers at the "end" of the table are still all correct, I'm struggling with how to properly filter the visual, or do I need to modify my measure(s) in some way?

Re: Measure causes strange behavior in Table Visual

Anonymous — Fri, 08 May 2020 15:10:53 GMT

Well, I kept reading and experimenting, and solved this one on my own. Turns out I needed to also add the additional value within the original calculate context. I had it in my head that I would want that operation outside of the Calculate Context, but after getting it to all work using a different (less elegant) approach, I went back and said "well, lets try putting the addition within the same calculate context" and viola! The visual started behaving the way I wanted it to, and the math was still all correct! Yay!

topic Measure causes strange behavior in Table Visual in DAX Commands and Tips

Measure causes strange behavior in Table Visual

Re: Measure causes strange behavior in Table Visual