Forum Discussion

vsslasd1's avatar
vsslasd1
Helper III
5 years ago
Solved

Dax New Table Query Get the Minimum Value from a column

I am trying to return only one value: The minimum value of a table, using the following syntax:

This is is the code that I need/want to use, but it returns the entire table for the column. I want to return only one value: the minimum period for this dataset:
DAX Statement:

Test =
//Requirement: Return the Min Period from a filtered table
var FilteredResults =FILTER('WIP-ALL', 'WIP-ALL'[Period]<>"Current")

Return SELECTCOLUMNS(FilteredResults,"Period",DATEVALUE('WIP-ALL'[Period]))

 

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi vsslasd1 

    Your requirement is to return the Min Period from a filtered table. What is the data type of your Period column, text or date type? I see you use DATEVALUE function in your first dax code. DATEVALUE function is to convert text type value to date type. So I think your Period value's format looks like "2021/01/01".

    What does "Current" mean in your filter ('WIP-ALL'[Period]<>"Current") ? If "Current" is the date value of today, you can use Today() function.

    Sample:

    You can build a measure to get the min period.

    Measure = MINX(FILTER(ALL('WIP-ALL'),'WIP-ALL'[PERIOD]<>TODAY()),'WIP-ALL'[PERIOD])

    Result:

    If you want to build a calcualted column, you can try this code.

    Column = CALCULATE(MIN('WIP-ALL'[PERIOD]),FILTER('WIP-ALL','WIP-ALL'[PERIOD]<>TODAY()))

    Result:

    Best Regards,

    Rico Zhou

     

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

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    vsslasd1 try this,

     

    Test = 
CALCULATE ( MIN ( Table[Period] ), ALL (), Table[Period] <> "Current" )

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • vsslasd1's avatar
      vsslasd1
      Helper III
      5 years ago

      Thank you.

      I tried that and I am receiving an error message:
      "The expression specifie in the query is not a valid table expression."

      I also tried this: 

      Test =
      //Requirement: Return the Min Period from a filtered table
      //var FilteredResults =FILTER('WIP-ALL', 'WIP-ALL'[Period]<>"Current")
      //var LookupValue(FilteredResults,Min(Period), Period<>"Current")
      //Return SELECTCOLUMNS(FilteredResults,"Period",DATEVALUE('WIP-ALL'[Period]))

      var a= CALCULATE ( MIN ( 'WIP-ALL'[Period] ), ALL (), 'WIP-ALL'[Period] <> "Current" )
      return a


      and received the same thing. 

    • vsslasd1's avatar
      vsslasd1
      Helper III
      5 years ago

      I believe it needs to be extracted from a table and converted to an individual value. I only need one column and one value for one row from the source dataset.

    • vsslasd1's avatar
      vsslasd1
      Helper III
      5 years ago

      I almost wonder/think that it should be a LookupValue??

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi vsslasd1 

    Your requirement is to return the Min Period from a filtered table. What is the data type of your Period column, text or date type? I see you use DATEVALUE function in your first dax code. DATEVALUE function is to convert text type value to date type. So I think your Period value's format looks like "2021/01/01".

    What does "Current" mean in your filter ('WIP-ALL'[Period]<>"Current") ? If "Current" is the date value of today, you can use Today() function.

    Sample:

    You can build a measure to get the min period.

    Measure = MINX(FILTER(ALL('WIP-ALL'),'WIP-ALL'[PERIOD]<>TODAY()),'WIP-ALL'[PERIOD])

    Result:

    If you want to build a calcualted column, you can try this code.

    Column = CALCULATE(MIN('WIP-ALL'[PERIOD]),FILTER('WIP-ALL','WIP-ALL'[PERIOD]<>TODAY()))

    Result:

    Best Regards,

    Rico Zhou

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi vsslasd1 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou