Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I am kind of stuck in this simple issue. I thought of using (calculate max all except) to arrive at the solution but somehow text values are not recognized. Can you please check this issue. I am so used to LOD min condition in tableau.
Current structure
Product | Country | Rep |
Furnitures | US | Rep-1 |
Furnitures | CA | Rep-2 |
Furnitures | GB | Rep-3 |
Furnitures | FR | Rep-4 |
Furnitures | IT | Rep-5 |
Electronics | GB | Rep-11 |
Electronics | CA | Rep-21 |
Electronics | US | Rep-31 |
Electronics | FR | Rep-41 |
Electronics | IT | Rep-51 |
Expected output with calculated column Lead (DAX)
Lead is updated based on rep for each product from Country US
Product | Country | Rep | Lead |
Furnitures | US | Rep-1 | Rep-1 |
Furnitures | CA | Rep-2 | Rep-1 |
Furnitures | GB | Rep-3 | Rep-1 |
Furnitures | FR | Rep-4 | Rep-1 |
Furnitures | IT | Rep-5 | Rep-1 |
Electronics | GB | Rep-11 | Rep-31 |
Electronics | CA | Rep-21 | Rep-31 |
Electronics | US | Rep-31 | Rep-31 |
Electronics | FR | Rep-41 | Rep-31 |
Electronics | IT | Rep-51 | Rep-31 |
Solved! Go to Solution.
hi @hbgv123 ,
you can add a calculated column like:
Column =
MAXX(
FILTER(
data,
data[Product]=EARLIER(data[Product])
&&data[Country]="US"
),
data[Rep]
)
it worked like:
Hi @hbgv123 ,
Explanation: We use FILTER function to create a virtual table that contains Rep for each Product's US Rep. Then we use the FIRSTNONBLANK function to get the value of the Rep. You can think of it as a MIN or MAX for text.
Lead =
VAR __Product = Data[Product]
VAR __Result =
FIRSTNONBLANK (
SELECTCOLUMNS (
FILTER ( ALL ( Data ), Data[Country] = "US" && Data[Product] = __Product ),
"Lead", Data[Rep]
),
[Lead]
)
RETURN
__Result
hi @hbgv123 ,
you can add a calculated column like:
Column =
MAXX(
FILTER(
data,
data[Product]=EARLIER(data[Product])
&&data[Country]="US"
),
data[Rep]
)
it worked like:
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |