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.
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.
Solved! Go to 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
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
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |