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 All,
Trying to come up with a logic to do following:
If unique ID exist in May data but not in Apr, then label it as "In" and show up $ value under "In" column
If the unique ID exists in Apr data but not in May then label it as "Exit" and show up $ value under "Exit" colummn
for everything else show 0.
Note: Data for two periods are appeneded
Below is more practical example of what I am trying to do and "Label", "In" and "Exit" are desired results.
Unique ID Month $ Label In Exit
678 May 13 0 0 0
985 May 15 In 15 0
462 May 16 0 0 0
678 Apr 13 0 0 0
876 Apr 17 Exit 0 17
462 Apr 22 0 0 0
I would really appreciate any help here.
Thanks
Solved! Go to Solution.
@Anonymous ,
Create two calculate columns using DAX below:
In = VAR Current_ID = Table1[Unique ID] VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID])) VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID)) VAR Current_Value = CALCULATE(MAX(Table1[$]), FILTER(Table1, Table1[Unique ID] = Current_ID)) RETURN IF(Number_Of_ID >= 2, 0, IF(Current_Month = "May", Current_Value, 0)) Exit = VAR Current_ID = Table1[Unique ID] VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID])) VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID)) VAR Current_Value = CALCULATE(MAX(Table1[$]), FILTER(Table1, Table1[Unique ID] = Current_ID)) RETURN IF(Number_Of_ID >= 2, 0, IF(Current_Month = "Apr", Current_Value, 0))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous ,
Have you solved your issue right now? If you have, could you please kindly mark the correct answer?
Regards,
Jimmy Tao
@Anonymous ,
Create two calculate columns using DAX below:
In = VAR Current_ID = Table1[Unique ID] VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID])) VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID)) VAR Current_Value = CALCULATE(MAX(Table1[$]), FILTER(Table1, Table1[Unique ID] = Current_ID)) RETURN IF(Number_Of_ID >= 2, 0, IF(Current_Month = "May", Current_Value, 0)) Exit = VAR Current_ID = Table1[Unique ID] VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID])) VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID)) VAR Current_Value = CALCULATE(MAX(Table1[$]), FILTER(Table1, Table1[Unique ID] = Current_ID)) RETURN IF(Number_Of_ID >= 2, 0, IF(Current_Month = "Apr", Current_Value, 0))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for all your help!
@v-yuta-msft wrote:@Anonymous ,
Create two calculate columns using DAX below:
In = VAR Current_ID = Table1[Unique ID] VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID])) VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID)) VAR Current_Value = CALCULATE(MAX(Table1[$]), FILTER(Table1, Table1[Unique ID] = Current_ID)) RETURN IF(Number_Of_ID >= 2, 0, IF(Current_Month = "May", Current_Value, 0)) Exit = VAR Current_ID = Table1[Unique ID] VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID])) VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID)) VAR Current_Value = CALCULATE(MAX(Table1[$]), FILTER(Table1, Table1[Unique ID] = Current_ID)) RETURN IF(Number_Of_ID >= 2, 0, IF(Current_Month = "Apr", Current_Value, 0))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous ,
Have you solved your issue right now? If you have, could you please kindly mark the correct answer?
Regards,
Jimmy Tao
Thank you so much!
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |