Forum Discussion
DAX : Create a filtered column based on another column
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
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.
5 Replies
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
V-lianl-msft 's solutions should work. 🙂
/ JAnonymous ,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/367415Hello 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
