Forum Discussion

quintans1's avatar
quintans1
Icon for Helper I rankHelper 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...
"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])
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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])
    )