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.
Dear all
I am trying to return different value in a new column if I find a specific value in several rows.
For example, I am trying to return the earliest view date when I found "Contact Customer Service" for each case and return, and if there is no any "Contact Customer Service" for a case, return the last view date.
Is it possible to have this loop funtion in Power BI?
Case | Time_Stamp | Action | First or Last View |
X001 | 2/1/2021 | View | 2/1/2021 |
X001 | 2/3/2021 | View | 2/1/2021 |
X001 | 2/18/2021 | Place into Cart | 2/1/2021 |
X001 | 2/19/2021 | Contact Customer Service | 2/1/2021 |
X001 | 2/22/2021 | Cancel | 2/1/2021 |
X002 | 1/3/2021 | View | 1/3/2021 |
X002 | 1/19/2021 | Place into Cart | 1/3/2021 |
X002 | 2/3/2021 | Purchase | 1/3/2021 |
X002 | 2/7/2021 | Paid | 1/3/2021 |
X003 | 3/2/2021 | View | 3/6/2021 |
X003 | 3/4/2021 | View | 3/6/2021 |
X003 | 3/5/2021 | Place into Cart | 3/6/2021 |
X003 | 3/6/2021 | View | 3/6/2021 |
X003 | 3/11/2021 | Paid | 3/6/2021 |
Thank you in advance!
Solved! Go to Solution.
View Date CC =
VAR __v =
FILTER(
'CASE',
'CASE'[Case] = EARLIER( 'CASE'[Case] ) && 'CASE'[Action] = "View"
)
RETURN
IF(
ISEMPTY(
FILTER(
'CASE',
'CASE'[Case] = EARLIER( 'CASE'[Case] ) && 'CASE'[Action] = "Contact Customer Service"
)
),
MAXX( __v, 'CASE'[Time_Stamp] ),
MINX( __v, 'CASE'[Time_Stamp] )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks for your answer! Yet, there is an error "A circulated dependency was detected..." now.
In your file, this error is not found, but the result seems different. 😞
Could you kindly take another look? Much appreciated!
View Date CC =
VAR __v =
FILTER(
'CASE',
'CASE'[Case] = EARLIER( 'CASE'[Case] ) && 'CASE'[Action] = "View"
)
RETURN
IF(
ISEMPTY(
FILTER(
'CASE',
'CASE'[Case] = EARLIER( 'CASE'[Case] ) && 'CASE'[Action] = "Contact Customer Service"
)
),
MAXX( __v, 'CASE'[Time_Stamp] ),
MINX( __v, 'CASE'[Time_Stamp] )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
The
MAXX( __v, 'CASE'[Time_Stamp] ), MINX( __v, 'CASE'[Time_Stamp] )
The MINX should come first and MAXX come second, but this DAX and logic work for me. Thanks again!
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |