Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello to all DAX gurus,
source_table:
Location (text) | Phase (text) | Id (number) | Value (number) |
A | P1 | 1 | 10 |
B | P1 | 2 | 10 |
B | P2 | 3 | 10 |
C | P2 | 4 | 10 |
C | P3 | 5 | 10 |
Logic:
For each location:
if Phase- P1, calculated (Value; FILTER (Source_table; Phase - P1));
if Phase- P2, is calculated (Value; FILTER (Source_table; Phase - P2));
if Phase- P3, calculated (Value; FILTER (Source_table; Phase - P3));
Explanation:
For each location, if there is a P1 value, return the value P1.
If there is no P1 value, use the value P2.
If there is no P2 value, use the P3 value.
How can I achieve a result table like the following, with the above logic, using only the measure? I use the SAAS model, so I can only create a new measure (no calculated columns).
Result_table:
Location | Phase | Id | new_measure |
A | P1 | 1 | 10 |
B | P1 | 2 | 10 |
C | P2 | 4 | 10 |
I tried to use RANKX, KEEPFILTER, ISFILTERED, but so har has not succeeded.
Thank you in advance 🙂
Solved! Go to Solution.
@Anonymous try this
Measure =
VAR __p1 = CALCULATE ( [Value Measure], Table[Phase] = "P1" )
VAR __p2 = CALCULATE ( [Value Measure], Table[Phase] = "P3" )
VAR __p3 = CALCULATE ( [Value Measure], Table[Phase] = "P3" )
RETURN
SWITCH ( TRUE(),
__p1 <> BLANK(), __p1,
__p2 <> BLANK(), __p2,
__p3
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @Anonymous ,
If your fact table have same order as sample, you could try the following DAX:
Measure =
IF (
SELECTEDVALUE ( 'Table'[Id] )
= CALCULATE ( MIN ( 'Table'[Id] ), ALLEXCEPT ( 'Table', 'Table'[Location] ) ),
[Your value]
)
Here is the result.
@v-eachen-msft thanks for the help, but the ID is random unfortunately, so I can't use MIN. I'm still trying to understand why I get all the Phase on the result table.
@Anonymous really not sure what does this means:
For each Location:
if Phase= P1, get Value;
else if Phase= P2, get Value;
else if Phase= P3, get Value
all are getting value?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous try this
Measure =
VAR __p1 = CALCULATE ( [Value Measure], Table[Phase] = "P1" )
VAR __p2 = CALCULATE ( [Value Measure], Table[Phase] = "P3" )
VAR __p3 = CALCULATE ( [Value Measure], Table[Phase] = "P3" )
RETURN
SWITCH ( TRUE(),
__p1 <> BLANK(), __p1,
__p2 <> BLANK(), __p2,
__p3
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k the result_table is correct if it only has Location, ID, and new_measure columns, but when I added the Phase column, it got messed up and showed duplicated Location, new_measure value for all the Phase P1-P3. How can I avoid this? Thanks!