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.
Hello,
I'm struggling with a DAX column calculation, I want to create a column based on a specific row, there are information about columns concerned :
- Event_Biztranslist : ID of parcel
- Event_Type : Type of event
- Event_Time : Date/Time of the event
- Readpoint_Type : "Tunnel" or "Reader"
- Rank : for each parcel, ranking events by Event_Time (first = most ancient, last = most recent)
This is the rule : for each [Event_Biztranslist] print the [Readpoint_Type] associated to the first "ObjectEvent"
To illustrate :
The result in the new column should be :
Reader
Reader
Reader
Reader
Reader
Tunnel
Tunnel
Tunnel
Regards,
Driss
Solved! Go to Solution.
Hi @Anonymous ,
Try this:
Column =
CALCULATE (
FIRSTNONBLANK ( 'Table (2)'[Relation_Type], MIN ( 'Table (2)'[Event_Time] ) ),
FILTER (
ALLEXCEPT ( 'Table (2)', 'Table (2)'[Event_Biztranslist] ),
'Table (2)'[Event_Type] = "OE"
)
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Try this:
Column =
CALCULATE (
FIRSTNONBLANK ( 'Table (2)'[Relation_Type], MIN ( 'Table (2)'[Event_Time] ) ),
FILTER (
ALLEXCEPT ( 'Table (2)', 'Table (2)'[Event_Biztranslist] ),
'Table (2)'[Event_Type] = "OE"
)
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Liang,
Many thanks, it works good for me 🙂
Btw, for info I find a solution through an intermediate calculation with boolean :
3. Parcels Reworked by Tunnel =
IF(Qualification[Event_Type] = "ObjectEvent"
&& Qualification[Readpoint_Type] = "Tunnel"
&& Qualification[Rank] > 1,
1,0)
Then :
X. Rework Mode =
VAR sum_check = CALCULATE(
SUM(Qualification[3. Parcels Reworked by Tunnel]),
ALLEXCEPT(Qualification, Qualification[Event_BizTransList])
)
RETURN
IF(sum_check = 1, "Tunnel" , "Reader")
Yours is more optimized
Best regards,
Driss
@Anonymous ,For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Hello @amitchandak
Thanks for your answer,
The rank had already done in the concerned columns, the idea is to return for each row of the parcel ID, the value of [Readpoint_type] associated to the "ObjectEvent" value
Driss
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |