topic Re: Dax not filtering second sales in DAX Commands and Tips

Dax not filtering second sales

FaizShaikh — Sat, 27 Jan 2024 12:18:22 GMT

Hi,

I just want to get the name of person whose sales is high and the title is red from list/ Prev column. But it give me blank.

Measure = CALCULATE(FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name]),'Table (2)'[Sales]=MAX('Table (2)'[Sales]) && 'Table (2)'[List/Prev]="Red")

Re: Dax not filtering second sales

speedramps — Sat, 27 Jan 2024 14:15:20 GMT

We want to help you but your description is too vaugue. Please write it again clearly.

Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That is a bit crazy. 😀

Please just give a simple non technical functional decscription of what you want, then let us suggest the solution. Thank you.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.

Remove any unneeded columns which may cause confussion.

Rename columns to user friendly names.
Also provide the example desired output, with a clear description of the process flow.
Remember not to share private data ... we don't want you to get into trouble. 😧
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Remember you are gerrting free expert help, so please put lots of proper effort to asking questions and providing example.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

Re: Dax not filtering second sales

FaizShaikh — Sat, 27 Jan 2024 16:08:06 GMT

Thanks for clarification.

Below is the precise information, also attaching sample for the same.

Table

Name Sales Red/Blue

Wayne	100	Blue
Rooney	300	Red
Deon	400	Blue
Carter	900	Red
Ron	600	Red
John	200	Red
Tom	150	Blue
Alison	250	Red

From the above table, I was trying to get the name of the person whose having the maximum sales and Red/Blue=Blue.

I am getting the name of a maximum sales person whose having blue criteria, but getting blank when I change crteria to red. (i.e red/blue= red gives me blank).

Also attaching Dax measures which I tried out.

Measure = CALCULATE(VALUES('Table'[Name]),FILTER(ALL('Table'),'Table'[Sales]=MAX('Table'[Sales]) &&'Table'[Red/Blue]="Blue"))

Measure 2= CALCULATE(FIRSTNONBLANK('Table'[Name],'Table'[Name]),'Table'[Sales]=MAX('Table'[Sales]) ,'Table'[Red/Blue]="Blue")

In above measures when I change Red/Blue="Red" it gives me blank.

Re: Dax not filtering second sales

speedramps — Sat, 27 Jan 2024 16:49:25 GMT

Try this

Click here to download PBIX from Onedrive

How it works

Create measures to get max red and blue value

Max red value = CALCULATE( MAX(yourdata[Sales]), ALL(yourdata), yourdata[Colour] = "Red")

Max blue value = CALCULATE( MAX(yourdata[Sales]), ALL(yourdata), yourdata[Colour] = "Blue")

Create measure to display all membets who have the max value

Names with red blue = var mymax = [Max red value] RETURN CALCULATE( CONCATENATEX(yourdata, yourdata[Name]," and "), yourdata[Sales] = mymax )

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos.

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button. Thank you.

Re: Dax not filtering second sales

FaizShaikh — Sun, 28 Jan 2024 10:56:41 GMT

Thankyou, Thanks .alot

Re: Dax not filtering second sales

speedramps — Sun, 28 Jan 2024 12:10:03 GMT

FaizShaikh please show your thanks by quickly clicking the Accept Solution AND the thumbs up button. 👍