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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
FaizShaikh
Regular Visitor

Dax not filtering second sales

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")
2 ACCEPTED SOLUTIONS

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
)
Names with red blue = 
var mymax = [Max red value]
RETURN
CALCULATE(
    CONCATENATEX(yourdata, yourdata[Name]," and "),
    yourdata[Sales] = mymax
)

speedramps_0-1706373932957.png

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.

 

View solution in original post

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

View solution in original post

5 REPLIES 5
FaizShaikh
Regular Visitor

Thankyou, Thanks .alot

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

FaizShaikh
Regular Visitor

Thanks for clarification.

 

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

 

Table

 Name    Sales    Red/Blue

Wayne100Blue
Rooney300Red
Deon400Blue
Carter900Red
Ron600Red
John200Red
Tom150Blue
Alison250Red

 

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.

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
)
Names with red blue = 
var mymax = [Max red value]
RETURN
CALCULATE(
    CONCATENATEX(yourdata, yourdata[Name]," and "),
    yourdata[Sales] = mymax
)

speedramps_0-1706373932957.png

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.

 

speedramps
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors