Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
27 | |
24 | |
23 |