Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct value from multiple columns

Hi All

 

I'm trying to create a DAX to get the unqiue rate per hour based on two columns - variety and shift. I will need to take this specific value and then use it again in another calculation. 

 

My current DAX doesn't work anymore as we now have 2 shifts so there are different rates per hour. My current DAX that needs to include the shift somehow:

1. Budget Rate by Variety = MAXX(DISTINCT('Variety Budget Data'[Variety]), MAX('Variety Budget Data'[Rate Per Hour]))

 

I've been trying to figure out how to get the distinct based on mutiple coloumns but have been going around in circles. 

 

Any help would be appreciated 🙂 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Use a selectcolumns to filter out only the columns that you need

    Budget Rate by Variety = MAXX(DISTINCT(
SELECTCOLUMNS('Variety Budget Data';"Variety";'Variety Budget Data'[Variety];"Shift";'Variety Budget Data'[Shift])), MAX('Variety Budget Data'[Rate Per Hour]))

    SELECTCOLUMNS allows you to filter out only the columns that you need

    There are other solutions, btw

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Use a selectcolumns to filter out only the columns that you need

    Budget Rate by Variety = MAXX(DISTINCT(
SELECTCOLUMNS('Variety Budget Data';"Variety";'Variety Budget Data'[Variety];"Shift";'Variety Budget Data'[Shift])), MAX('Variety Budget Data'[Rate Per Hour]))

    SELECTCOLUMNS allows you to filter out only the columns that you need

    There are other solutions, btw

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

       

      Thank you for the reply. I think the formula works, though my Power BI model is giving me headaches as I cannot get the relationships to work.

       

      Thanks again

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

       

      Would there be a better function rather than using "MAX"? Would sumx be better as it would take the row context?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi,

        MAX and SUMX are two very different functions. But even MAXX and MAX are very different.

        In this specific case you can't use SUMX as it takes a table, rather than a column like MAX.

         

        Unfortunately without seeing the model I can't be of more help.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , if the above solution does not help. Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.