Forum Discussion

Tiitti7's avatar
Tiitti7
Frequent Visitor
1 year ago
Solved

Problems with creating the measure

I need to create a measure that returns the smallest "CostID" value for each distinct ID and ignores all other values.

 

CostID represents the order start date in this case, and there is no separate start date in the data model. In the chart, the X-axis should display the year and month, while the Y-axis should show the order date based on this measure.

 

In this case, there are 2 orders with start dates of 2020-01 and 2020-03.

 

Is that possible? 



 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Tiitti7 

    Based on your information, I create a sample table:

    Then create new measure and try the following DAX:

    SmallestCostID = 
CALCULATE(
    MIN('Table'[CostID]),
    ALLEXCEPT('Table', 'Table'[ID])
)

     

    Create visual and put this measure in it, here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    Tiitti7 , Try using ALLEXCEPT

     

    MinCostID =
    CALCULATE(
    MIN(Table[CostID]),
    ALLEXCEPT(Table, Table[ID])
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Tiitti7 

    Based on your information, I create a sample table:

    Then create new measure and try the following DAX:

    SmallestCostID = 
CALCULATE(
    MIN('Table'[CostID]),
    ALLEXCEPT('Table', 'Table'[ID])
)

     

    Create visual and put this measure in it, here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.