Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dax help

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 co...
Need Help
Show and Tell
Tips and Tricks
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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"
    )

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

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"
    )