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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
DaveCor
Helper I
Helper I

Do I need to use RANK for this or is there another, better way?

I'm struggling to understand how I need to approach this, so any tips would be appreciated. I can usually figure stuff out but in this case I'm lost as I can't understand what approach would be needed for the answer to this. My best guess was to try using RANK to apply a seniority over the values but that was a dead end so I'm stuck. 

 

I have a simple table visual of data in Power BI, just showing the values of columns from a source table which looks something like this

 

sold-to numbercustomer nameTrained Status

123LondonTrained
123LondonNot Trained
128New YorkNot Trained
128New YorkTrained
456BerlinUnknown Status
489ParisNot Trained
489ParisNot Trained
489ParisNot Trained
654MadridUnknown Status
789RomeTrained
879OsloNot Trained

 

Its possible for there to be multiple rows where a customer can be both "Trained" and "Not Trained" (See London, New York & Paris above). 

 

What I need to do is have the table display only a single value for each customer where the following applies

1. If both "Trained" and "Not Trained" are listed for a customer the "Trained" value takes precedence and should be the returned value 

2. Otherwise return a single occurance of the relevant value so that each customer is listed only once in the table

 

The end result would look something like this

 

sold-to numbercustomer nameTrained Status

123LondonTrained
128New YorkTrained
456BerlinUnknown Status
489ParisNot Trained
654MadridUnknown Status
789RomeTrained
879OsloNot Trained

 

 

1 ACCEPTED SOLUTION
speedramps
Super User
Super User

Try this ...

 

Answer =
VAR Trained = CALCULATE(SELECTEDVALUE(Facts[Status]), (Facts[Status]) = "Trained")
RETURN
IF( ISBLANK(Trained), SELECTEDVALUE(Facts[Status]), Trained)
 
 

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

 

One question per ticket please. If you need to 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 ! 

 
 

View solution in original post

2 REPLIES 2
speedramps
Super User
Super User

Try this ...

 

Answer =
VAR Trained = CALCULATE(SELECTEDVALUE(Facts[Status]), (Facts[Status]) = "Trained")
RETURN
IF( ISBLANK(Trained), SELECTEDVALUE(Facts[Status]), Trained)
 
 

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

 

One question per ticket please. If you need to 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 ! 

 
 

Much obliged @speedramps that worked perfectly. 

 

I didn't think to go that route to a solution so I appreciate you taking the time. I'll know for next time 🙂

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.