How to use Contains with condition in Summarize

[a-rychen]

Hi guys,

I am new to power BI, and got a DAX problem.

I Have Historic data as below

[Historic Table]

GUID      |  Date  |  DeviceID  |   Alert

----------------------------------------------------

AAAAA   | 3/27   |        a           |      1

BBBBB    | 3/27   |        a           |      0

CCCCC   | 3/27   |        b           |      0

DDDDD  | 3/26   |        a           |      1

EEEEEE    | 3/26   |        a           |      0

FFFFFF    | 3/26   |        b           |      1

-----------------------------------------------------

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)  from the historic Data.

Just like the table below.

[Device Summary]

DeviceID   | MAX Date        |   Alert

----------------------------------------------------

a                |      3/27              |        True  

b                |      3/27              |        False     

-----------------------------------------------------

So I use below DAX to create table,

DeviceSummary = ( 

  SUMMARIZE(

    Historic, Historic[DeviceID],

    "MAX Date", MAX('Historic[Date]),

    "Alert",IF(

      CONTAINS('Historic','Historic[Alert],1),"True","False"

    )

  )

)

But I got the wrong result as below

DeviceID   | MAX Date        |   Alert

----------------------------------------------------

a                |      3/27              |        True  

b                |      3/27              |        True

-----------------------------------------------------

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.

[v-shex-msft · Accepted solution]

Hi @a-rychen ,

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] )
                && [Date] = EARLIER ( [L Date] )
                && [Alert] = 1
        )
    ) > 0
)

Regards,

Xiaoxin Sheng

[a-rychen]

Hi, @v-shex-msft 

It perfectly resolve my problem, Thank you.