Forum Discussion

tha_fanatic's avatar
tha_fanatic
New Member
11 months ago
Solved

Indexing Not working Properly - Dax Measure

Hi, 

I'm working on vehicle Mileage Data but my measure is returning an unexpected result in the 1st cell of the table as shown below. The Calculation is Mileage at Teco MINUS Prev. Mileage = MileageDiff. Prev.Mileage & Mileage Diff first rows need to be blank.

Looks like the measure is picking up the Max Mileage at TECO and using it to fill up the first cell in Prev.Mileage to get the 'Mileage Diff'. The first cell in 'Prev.Mileage' and 'Mileage Diff' need to be blank. How do I prevent this from happening? Please help

 

  • KNP's avatar
    KNP
    11 months ago

    Try this...

     

    Previous Mileage = 
VAR __currentTECO = SELECTEDVALUE('Vehicle Mileage'[Mileage at TECO])
VAR __currentEquipment = SELECTEDVALUE('Vehicle Mileage'[Equipment])
VAR __currentMntPlan = SELECTEDVALUE('Vehicle Mileage'[MntPlan])
VAR __result =
    CALCULATE(
        MAX('Vehicle Mileage'[Mileage at TECO])
        ,FILTER(
            ALL('Vehicle Mileage')
            ,'Vehicle Mileage'[Equipment] = __currentEquipment
                && 'Vehicle Mileage'[MntPlan] = __currentMntPlan
                && 'Vehicle Mileage'[Mileage at TECO] < __currentTECO
        )
    )
RETURN __result

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    11 months ago

    Hi,

    I tried to create a sample pbix file like below, and tried to use offset dax function in the measure.

    Please check the below picture and the attached pbix file.

     

     

     

    Prev. mileage: = 
SUMX (
    OFFSET (
        -1,
        ALL (
            vehicle[equipment],
            vehicle[mintplan],
            vehicle[uniqueID],
            vehicle[mileage]
        ),
        ORDERBY ( vehicle[mintplan], ASC ),
        ,
        PARTITIONBY ( vehicle[mintplan], vehicle[equipment] )
    ),
    vehicle[mileage]
)

4 Replies

  • KNP's avatar
    KNP
    Icon for Super User rankSuper User
    11 months ago

    Try this...

     

    Previous Mileage = 
VAR __currentTECO = SELECTEDVALUE('Vehicle Mileage'[Mileage at TECO])
VAR __currentEquipment = SELECTEDVALUE('Vehicle Mileage'[Equipment])
VAR __currentMntPlan = SELECTEDVALUE('Vehicle Mileage'[MntPlan])
VAR __result =
    CALCULATE(
        MAX('Vehicle Mileage'[Mileage at TECO])
        ,FILTER(
            ALL('Vehicle Mileage')
            ,'Vehicle Mileage'[Equipment] = __currentEquipment
                && 'Vehicle Mileage'[MntPlan] = __currentMntPlan
                && 'Vehicle Mileage'[Mileage at TECO] < __currentTECO
        )
    )
RETURN __result

     

     

    • tha_fanatic's avatar
      tha_fanatic
      New Member
      11 months ago

      Hi KNP,

       

      This solution worked for me. I appreciate your help.

      Kind Regards,

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    11 months ago

    Hi,

    I tried to create a sample pbix file like below, and tried to use offset dax function in the measure.

    Please check the below picture and the attached pbix file.

     

     

     

    Prev. mileage: = 
SUMX (
    OFFSET (
        -1,
        ALL (
            vehicle[equipment],
            vehicle[mintplan],
            vehicle[uniqueID],
            vehicle[mileage]
        ),
        ORDERBY ( vehicle[mintplan], ASC ),
        ,
        PARTITIONBY ( vehicle[mintplan], vehicle[equipment] )
    ),
    vehicle[mileage]
)
    • tha_fanatic's avatar
      tha_fanatic
      New Member
      11 months ago

      Hi Jihwan,

       

      This worked a treat. Many thanks for your help.