DAX query for counting values based on another cumulative DAX measure

Question

I have this table:

Id   Length(m)   Defect  Site   Date
1    10          1       y      10/1/19
2    60          0       x      09/1/19
3    30          1       y      08/1/19
4    80          1       x      07/1/19
5    20          1       x      06/1/19

I want to count the amount of defects and ids that are in the last 100m of length(sorted by date DESC), whilst maintaining the ability for this to change with additional filters. For example, what are the amount of defects for site x in the last 100m, or what are the amount of defects in the last 100m that have an ID bigger than 1.

For the question 'What are the amount of defects for site x in the last 100m', I would like the result to be 2, as the table should look like this:

Id   Length(m) Length Cum.   Defect  Site   Date
4    80        80            1       x      07/1/19
5    20        100           1       x      06/1/19

I believe the issue in creating this query so far has been that I need to create a cumulative DAX query first and then base the counting query off of that DAX query.

Also important to note that the filtering will be undertaken in PowerBI. I don't want to hardcode filters in the DAX query.

Any help is welcome.

janvanwerkhoven janvanwerkhoven · Accepted Answer · 2019-09-25T15:13:38

Allwright!

I have taken a crack at this. I did assume that the id of the items(?) increments through time, so the oldest item has the lowest id.

You were correct that we need to filter the table based on the cumulative sum of the meters. So I first add a virtual column to the table (CumulativeMeters) which I can then use to filter the table on. I need to break the filter context of the ADDCOLUMNS function to sum up the hours of multiple rows.

Important is to use ALLSELECTED to keep any external filters in place. After this it is pretty straightforward to filter the table on a maximum CumulativeMeters of <= 100 meters and where the row is a defect. Counting the rows in the resulting table gives you the result you are looking for:

# Defects last 100m = 
CALCULATE (
    COUNTROWS ( Items ),
    FILTER (
        ADDCOLUMNS (
            Items,
            "CumulativeMeters", CALCULATE (
                SUM ( Items[Length(m)] ),
                FILTER (
                    ALLSELECTED(  Items ),
                    Items[Date] <= EARLIER ( Items[Date] )
                        && Items[Id] <= EARLIER ( Items[Id] )
                )
            )
        ),
        [CumulativeMeters] <= 100
            && Items[Defect] = 1
    )
)

Hope that helps,

Jan

DAX query for counting values based on another cumulative DAX measure

1 Answers