Forum Discussion

quintans1's avatar
quintans1
Helper I
3 years ago
Solved

Calculated Column to provide a minimum numerical value based on multiple criteria

I need assistance in creating a calculated column that will show the minimum value (Location Number) based on multiple criteria (Category Number, Country, Location Number). I am hoping I can also see the minimum numerical value when a row has no value.

 

So for each Category/Country combo, the calculated column returns back the minimum Location Number, I need it to provide the minimum location number in the event the Location Number is blank.

 

"Calculated Column"
"DAX"
"Need Help"
  • quintans1's avatar
    quintans1
    3 years ago

    Thank you very much, the dax for the column works in your PBIX, but when I try to employ what you wrote to my work pbix, the calculated column partially works. Confusing since I see that it works in what you provided.

     

    I ended up using DAX like this below in my work pbix and I get the same result in your dax and works completely in my work pbix.

    test_column = CALCULATE (
        MIN (Data[Location Number]),
        ALLEXCEPT (Data,Data[Category Number],Data[Country])
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi, I am not sure if I understood you question correctly, but please check the below picture and the attached pbix file.

     

     

     

    Expected result CC =
CALCULATE (
    MIN ( Data[Location Number] ),
    INDEX (
        1,
        FILTER (
            SUMMARIZE ( Data, Data[Category Number], Data[Country], Data[Location Number] ),
            Data[Location Number] <> BLANK ()
        ),
        ORDERBY ( Data[Location Number], ASC ),
        DEFAULT,
        PARTITIONBY ( Data[Category Number], Data[Country] )
    )
)

     

    • quintans1's avatar
      quintans1
      Helper I
      3 years ago

      Thank you very much, the dax for the column works in your PBIX, but when I try to employ what you wrote to my work pbix, the calculated column partially works. Confusing since I see that it works in what you provided.

       

      I ended up using DAX like this below in my work pbix and I get the same result in your dax and works completely in my work pbix.

      test_column = CALCULATE (
          MIN (Data[Location Number]),
          ALLEXCEPT (Data,Data[Category Number],Data[Country])
      )