How to use Contains with condition in Summarize

Question

Hi guys,&nbsp;I am new to power BI, and got a DAX problem.&nbsp;I Have Historic data as below&nbsp;[Historic Table]&nbsp;GUID&nbsp; &nbsp; &nbsp; |&nbsp; Date&nbsp; |&nbsp; DeviceID&nbsp; |&nbsp;&nbsp; Alert----------------------------------------------------AAAAA&nbsp; &nbsp;| 3/27&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1BBBBB&nbsp; &nbsp; | 3/27&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0CCCCC&nbsp; &nbsp;| 3/27&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0DDDDD&nbsp; | 3/26&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1EEEEEE&nbsp; &nbsp; | 3/26&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0FFFFFF&nbsp; &nbsp; | 3/26&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1-----------------------------------------------------&nbsp;And I want to create a new table called Device Summary which summarized the device ID and latest date , and Alert (If any is 1 in the same date , than 1)&nbsp; from the historic Data.&nbsp;Just like the table below.&nbsp;[Device Summary]&nbsp;DeviceID&nbsp;&nbsp; | MAX Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; Alert----------------------------------------------------a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3/27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; True&nbsp;&nbsp;b&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3/27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; False&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-----------------------------------------------------&nbsp;So I use below DAX to create table,&nbsp;DeviceSummary = (&nbsp;&nbsp; SUMMARIZE(&nbsp; &nbsp; Historic, Historic[DeviceID], &nbsp; &nbsp; "MAX Date", MAX('Historic[Date]),&nbsp; &nbsp; "Alert",IF(&nbsp; &nbsp; &nbsp; CONTAINS('Historic','Historic[Alert],1),"True","False"&nbsp; &nbsp; )&nbsp; ))&nbsp;But I got the wrong result as below&nbsp;DeviceID&nbsp;&nbsp; | MAX Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; Alert----------------------------------------------------a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3/27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; True&nbsp;&nbsp;b&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3/27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; True-----------------------------------------------------&nbsp;Looks like "CONTAINS" operator search all the data row in Historic Table, how can I use "Contains" with condition which just find data in latest date.

anonymous · Accepted Answer

Hi&nbsp;a-rychen&nbsp;,
Please use following calculate table formula to create summary table:
Summary table =
ADDCOLUMNS (
    SUMMARIZE ( Table1, [DeviceID], "L Date", MAX ( Table1[Date] ) ),
    "Alert", COUNTROWS (
        FILTER (
            ALL ( table1 ),
            [DeviceID] = EARLIER ( [DeviceID] )
                &amp;&amp; [Date] = EARLIER ( [L Date] )
                &amp;&amp; [Alert] = 1
        )
    ) &gt; 0
)

Regards,
Xiaoxin Sheng

Forum Discussion

How to use Contains with condition in Summarize

2 Replies

Related content

Parameter in summarize

SUMMARIZE TABLE with specific conditions ?

Don't Summarize....still summarizes...?

Conditional Summarize Table

Contain Word