Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to 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 , 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.
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
Hi @Anonymous
Would there be a better function rather than using "MAX"? Would sumx be better as it would take the row context?
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |