Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

Variety Lookups.PNG

 

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 🙂 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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
Not applicable

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
Not applicable

Hi @Anonymous 

 

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

Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.