Dax help

[Anonymous]

Hi I need help trying so how many repairs are logged against an address.

My data set contains data from the past 5 years

Currently I have the overall total which I had done by using SQL to count based on address reference 'Repair Count' [Count of Repairs]

The idea is that when you filter by year the measure will take into account the values per year i.e.

House 1 – had 15 repairs raised in 2017 so the group it falls into for that year is “Between 10 and 15"

However if we were looking at 2016, 2017 & 2019 House 1 had 45 repairs raised therefore the group it falls into is “Between 30 and 90"

The dax for the groupings is below for reference.

Number Of Repairs Groups =

VAR x = 'Repair Count' [Count of Repairs]

RETURN

SWITCH(

   TRUE(),

   x >= 0 && x <= 5, "Between 0 and 5",

   x >= 6 && x <= 10, "Between 6 and 10",

   x >= 10 && x <= 30, "Between 10 and 30",

   x >= 30 && x <= 90, "Between 30 and 90",

   x >= 90 && x <= 150, "Between 90 and 150",

   "150+ and above"

)

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

I think a measure is suitable because you want a dynamic value.

Is 'Repair Count' [Count of Repairs] the number of repairs per address per year? If it is, you could try this measure.

Measure =
VAR x =
    CALCULATE (
        SUM ( 'Repair Count' [Count of Repairs] ),
        ALLEXCEPT ( 'Repair Count', 'Repair Count'[address], 'Repair Count'[year] )
    )
RETURN
    SWITCH (
        TRUE (),
        x >= 0
            && x <= 5, "Between 0 and 5",
        x >= 6
            && x <= 10, "Between 6 and 10",
        x >= 10
            && x <= 30, "Between 10 and 30",
        x >= 30
            && x <= 90, "Between 30 and 90",
        x >= 90
            && x <= 150, "Between 90 and 150",
        "150+ and above"
    )

[kentyler]

Would looking at the "average # per year" help.

That way it would 15 for one year, and 15 for the three years as well.

[Nathaniel_C]

Hi @Anonymous ,

You may be able to drop the date on a matrix, and then drop your other measure Number of Repair Groups as values. 

or House number on Rows, then Years on Columns, then your measure on Values

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel