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.
Not sure how to approach this in PowerBI. If this were a normal imperative programming language, I would just write a for-each loop.
I have an excel sheet of data in a Power BI report.
Quote Number Winner Shop External Shop
145 Y ABC
145 N TES
145 N CAR
647 N CAR
647 Y TES
647 N ABC
785 Y ABC
546 N TES FEF
There's a bunch of quote offers from different shops and sometimes even an external shop. Only one shop can win each quote.
I was thinking that I could create a new column in PowerBI datasource called "Winner Name".
Then what I would like to do is loop thru each quote to assign the winner instead of just displaying Y/N.
For each QuoteNumber,
If Winner = "Y", then for that QuoteNumber, grab Shop "ABC" and set WinnerName = ABC
and
If ExternalShop != blank, then WinnerName = FEF
The end results I want is:
Quote Number Winner Shop External Shop Winner Name
145 Y ABC ABC
145 N TES ABC
145 N CAR ABC
647 N CAR TES
647 Y TES TES
647 N ABC TES
785 Y ABC ABC
546 N TES FEF FEF
I'm trying to make it so if a shop filters for their Shop name, the table on my report displays who they lost to on all of their quotes.
Winner Name would immediately show them if they won or who they lost to.
How do I do something like this in PowerBI?
Solved! Go to Solution.
@test2sa I assume you are trying to add a calculated column to the data model? In that case you can use this:
WinnerName =
VAR Winner =
CALCULATE (
SELECTEDVALUE ( 'Table'[Shop] ),
'Table'[Winner] = "Y",
ALLEXCEPT ( 'Table', 'Table'[Quote Number] )
)
RETURN
IF (
'Table'[External Shop] = "FEF",
"FEF",
Winner
)
@test2sa I assume you are trying to add a calculated column to the data model? In that case you can use this:
WinnerName =
VAR Winner =
CALCULATE (
SELECTEDVALUE ( 'Table'[Shop] ),
'Table'[Winner] = "Y",
ALLEXCEPT ( 'Table', 'Table'[Quote Number] )
)
RETURN
IF (
'Table'[External Shop] = "FEF",
"FEF",
Winner
)
Thank you!! I've almost got it working. I'm having trouble with that IF statement at the very end.
IF (
NOT(ISBLANK('Table'[External Shop])),
'Table'[External Shop],
Winner
)
I've tried writing it a bunch of different ways and it wants to return only Winner or only External Shop. It won't return the correct one for each quote.
@test2sa you can do IF ( 'Table'[External Shop] <> "", 'Table'[External Shop] , Winner )
Thank you!! That worked perfectly!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |