DAX : Create a filtered column based on another column

[Anonymous]

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

@tex628 

Regards, 

Driss

[tex628]

@V-lianl-msft 's solutions should work. 🙂

/ J

[V-lianl-msft · Accepted 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.

[Anonymous]

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

[amitchandak]

@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

[Anonymous]

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