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.
Hi guys,
I hope you can help me find a solution to this case. The result that I want is the FINAL AMOUNT column. Thanks a lot! I could've done this in the Power Query editor but I am using data connected to Azure via Direct Query.
Hi @KrisG,
Try measure as:
Final Amount =
IF(
MAX('Table'[Offer is Selected])="True",
MAX('Table'[Amount]),
IF(
MAX('Table'[Amount])=0 &&
COUNTROWS(FILTER(
ALL('Table'),
'Table'[Project Name]=MAX('Table'[Project Name])
))=1,
0,
MINX(
FILTER(
ALL('Table'),
'Table'[Project Name]=MAX('Table'[Project Name]) && 'Table'[Amount]>0
),
'Table'[Amount]
)
)
)
Here is the output:
The pbix is attached.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Hi @v-xulin-mstf,
Sorry for the late reply and thanks for the help. Anyways, I tried the measure and it still doesn't give me the right result.
Looking at your ouput, all seems to be right except for Project A. If a project has TRUE and FALSE "Offer is Selected", the final amount should be the TRUE offer only. Instead of Final Amount as (1, 2, 1), it should be (2) only.
@KrisG
Do you need a measure or a calculated column?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy , is it possible to have both options? If not, then a measure will be great.
@KrisG
The Measure:
Final Amount M =
var __proj = SELECTEDVALUE(Table2[Project Name])
var __amount = SELECTEDVALUE(Table2[Amount])
var __offerselected = SELECTEDVALUE(Table2[Offer is Selected])
return
SWITCH(
TRUE(),
__offerselected = TRUE() , __amount,
ISEMPTY(
FILTER(
ALLSELECTED(Table2),Table2[Project Name]=__proj && Table2[Offer is Selected]=TRUE()
)
),
if(
__amount =
MINX(
FILTER(
ALLSELECTED(Table2),Table2[Project Name]=__proj && Table2[Offer is Selected]=FALSE() && Table2[Amount] > 0
),
Table2[Amount]
),
__amount
)
)
Calculated Column:
Final Amount =
var __proj = Table2[Project Name]
var __amount = Table2[Amount]
var __offerselected = Table2[Offer is Selected]
return
SWITCH(
TRUE(),
__offerselected = TRUE() , __amount,
ISEMPTY(
FILTER(
Table2,Table2[Project Name]=__proj && Table2[Offer is Selected]=TRUE()
)
),
if(
__amount =
MINX(
FILTER(
Table2,Table2[Project Name]=__proj && Table2[Offer is Selected]=FALSE() && Table2[Amount] > 0
),
Table2[Amount]
),
__amount
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy thanks for the codes but the result is incorrect. I think it has to do with the MINX function.
Please take into consideration that a project can have more than one offer and an offer can have more than one technique.
Here's the code with just minor label changes from my actual query.
@KrisG
Sorry, not clear enough. provide sample data as you did but including more records with the scenarios you just mentioned along with the expected results.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |