Power BI Dax formula - Sum in table problem

Question

I have a table in a report which shows the number of open cases by age group at the end of each month, this uses an age group table and a dates table which are separate from the main data table (called Child in the formula below). It seems to work except for the totals in the table which are behaving very strangely I have looked for solutions here and kind of understand why totals don't work in power bi tables because of contexts but can't understand why I am getting such low figures here and what they are based on.

My formula is this:

Number Children by Age Group = 
VAR AgeMin = SELECTEDVALUE ( 'Age Group Table'[AgeMin] )
VAR AgeMax = SELECTEDVALUE ( 'Age Group Table'[AgeMax] )
RETURN
CALCULATE (
    DISTINCTCOUNT ( 'Child'[Person ID] ),
    FILTER (
        'Child',
        'Child'[Start_Date] <= MAX ( Dates[Date] )
            && ('Child'[End_Date] >= MAX ( Dates[Date] ) || ISBLANK ( 'Child'[End_Date] ) )
            && FLOOR ( DATEDIFF ( 'Child'[Date of Birth], MAX ( Dates[Date] ), DAY ) / 365.25, 1 ) >= AgeMin
            && FLOOR ( DATEDIFF ( 'Child'[Date of Birth], MAX ( Dates[Date] ), DAY ) / 365.25, 1 ) <= AgeMax
    )
)

and the results I am getting in my table are:

Sample Result Screenshot

If I put a sumx around the formula the totals go up to around 180, higher but still far too low I don't have to have the totals in the table but I would like to be able to understand what is going on to increase my knowledge of dax formulae

Does using MIN and MAX rather than SELECTEDVALUE twice when defining your variables change the total? — Alexis Olson

Seymour Seymour · Accepted Answer · 2020-04-13T19:23:08

In general, it is always useful to provide:

small set of sample data
relevant information about the data model

So it is easier for folks to replicate your error and test the solution. Nevertheless, I have seen a situation like this many time and the standard process to solve it is as follow:

Set-up DAX Studio in order to run DAX Queries (you will see why later)

Use Performance Analyzer to extract the query to generate that table. Which will look similar to:

DEFINE  /* this defines report-level measures and filter context */
MEASURE Table1[Measure1] = ...

VAR __dsfiltertable0 = ...

VAR __dsfiltertable1 = ...
...
EVALUATE  /* this evaluates the data used in the visualization */
...```

Now, you can modify the query in order to extract (1) the one-column table of 'Child'[Person Id] which is used to obtain the value in January&0-4years and (2) the one-column table 'Child'[Person Id] which is used to obtain the value in January&Total.
You can easily export this table in Excel and investigate what is the issue.

If you are lost, you can share the query extracted using Performance Analyzer and I can generate you the queries to launch in DAX Studio.

Power BI Dax formula - Sum in table problem

1 Answers