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,
I'd like to count rows with multiple conditions. The first measure seems to work well. But the second one doesn't work. The only thing I changed is the time difference, from 10s to 2hr. Event V2 count should return as 1.
Events = VAR y = ADDCOLUMNS(
TABLE,"Batch",
1-CALCULATE(
COUNTROWS(TABLE),
FILTER(
TABLE,
TABLE[SITE_NO]=EARLIER(TABLE[SITE_NO]) &&
TABLE[START_DT]>EARLIER(TABLE[START_DT]) &&
TABLE[EVENT_CODE]=EARLIER(TABLE[EVENT_CODE]) &&
DATEDIFF(EARLIER(TABLE[END_DT]),TABLE[START_DT],SECOND)<10
)))
RETURN SUMX(
y,[Batch])
Events V2 = VAR y = ADDCOLUMNS(
TABLE,"Batch",
1-CALCULATE(
COUNTROWS(TABLE),
FILTER(
TABLE,
TABLE[SITE_NO]=EARLIER(TABLE[SITE_NO]) &&
TABLE[START_DT]>EARLIER(TABLE[START_DT]) &&
TABLE[EVENT_CODE]=EARLIER(TABLE[EVENT_CODE]) &&
DATEDIFF(EARLIER(TABLE[END_DT]),TABLE[START_DT],SECOND)<7201
)))
RETURN SUMX(
y,[Batch])
Please find another example below. Event V2 should return as 6.
Hi @Anonymous ,
I think you need to show the expected result of your sample data.
I use your sample data to create a calculated column, hope that's what you want, please try.
Column = SUMX(Sheet1,IF([SITE_NO]=EARLIER([SITE_NO]) &&
[START_DT]>EARLIER([START_DT]) &&
[EVENT_CODE]=EARLIER([EVENT_CODE]) &&
DATEDIFF(EARLIER(Sheet1[END_DT]),Sheet1[START_DT],SECOND) <= 10,
1,
0))
Aiolos Zhao
Proud to be a Super User!
Hi @AiolosZhao
Please read message 5 for expected output. The sample data size is so tiny compared to the one I am working on.
I am afraid I won't be able to add the calculated column for years worth of data (approx. 10M rows). That's why I wanted DAX measure to combine repeated events with above conditions.
Hi @Anonymous ,
What's your the expected result of your sample data, in your sample data, the Site No is A / B / C, NO 4780 and 3841
And I use the measure for SITE NO = B and EVENT_CODE = OB, the result is 24 and 27.
Aiolos Zhao
Proud to be a Super User!
Hi @AiolosZhao
By site 4780 and 3841, I was referring to screenshots in my message 1. Anyway, as for sample data, the expected output (site B, event OB) should be 27. I think you created the right measure.
Hi @Anonymous ,
The measures are:
Measure = VAR y = ADDCOLUMNS(
Sheet1,"Batch",
CALCULATE(
COUNTROWS(Sheet1),
FILTER(
Sheet1,
[SITE_NO]=EARLIER([SITE_NO]) &&
[START_DT]>EARLIER([START_DT]) &&
[EVENT_CODE]=EARLIER([EVENT_CODE]) &&
DATEDIFF(EARLIER([END_DT]),[START_DT],SECOND)<=7200)
))
RETURN SUMX(
y,[Batch])
Measure 2 = VAR y = ADDCOLUMNS(
Sheet1,"Batch",
CALCULATE(
COUNTROWS(Sheet1),
FILTER(
Sheet1,
[SITE_NO]=EARLIER([SITE_NO]) &&
[START_DT]>EARLIER([START_DT]) &&
[EVENT_CODE]=EARLIER([EVENT_CODE]) &&
DATEDIFF(EARLIER([END_DT]),[START_DT],SECOND)<=10)
))
RETURN SUMX(
y,[Batch])
Please try.
Aiolos Zhao
Proud to be a Super User!
Hi @AiolosZhao
Thanks for quick reply. Your measures are similar to mine in first post.
However, in large dataset, Measure 2 works but somehow Measure doesn't work.
Hi @Anonymous ,
I think if the columns are not changed in your large data, the measure will also work.
You may need to show the data model, columns in the tables, and selections in the dashboard, and the case which doesn't work.
That may help others to figure out the problem.
Aiolos Zhao
Proud to be a Super User!
@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. Along with Formula logic
Hi,
Please download the sample pbix from gdrive.
https://drive.google.com/file/d/1g0__wX8wCgRGSLMwzDWuzR8a5zWki09n/view?usp=sharing
@Anonymous , Checked out the pbix. But not able to get the wrong data as it not the same(sample data) as the one you shared on the post.
Please share the expected output
Also try
countx(values(Sheet[SITE_NO]) ,calculate(COUNTROWS(Sheet1),
FILTER(
Sheet1,
[SITE_NO]=max([SITE_NO]) &&
[START_DT]>max([START_DT]) &&
[EVENT_CODE]=max([EVENT_CODE]) &&
DATEDIFF(min(EARLIER([END_DT])),max([START_DT]),SECOND)<10) , allselected(Sheet1)
))
Hi @amitchandak
Thanks for swift replies. I think I should clarify more.
Basically, I am trying to combine the repeated events, however they are recorded as separate rows.
My expected output for Event V2 would be:
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |