Distinct value from multiple columns

[Anonymous]

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 🙂 

[amitchandak]

@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.

[Anonymous · Accepted solution]

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]

Hi @Anonymous 

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

[Anonymous]

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.

[Anonymous]

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