Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
rpinxt
Impactful Individual
Impactful Individual

Count as 1 when it happens in the same minute

Sorry for the really abstract title but this is a bit of a strange one.

We have this :

rpinxt_0-1712746351620.png

For the first 2 lines you see there is 12 seconds between them.

I made Bins to group everything in the same minute because I want the EA/Carton field to show 1.

So if it was confirmed in the same minute it is only 1 occurence.

When I take out confirmation time everything looks ok :

rpinxt_1-1712746481761.png

2:27 showing only 1 EA/Carton. But the field is not summed. And of course this is only 1 line.

In the end I want to sum the occurences and make sure that everything in the same minute is counted as 1.

But when I put sum back on on the field EA/Carton :

rpinxt_2-1712746584590.png

 

Then it still counts as 2.

 

Would there be a good way to count everything that happens in the same minute as 1 occurence?

 

Here a snippet test data to play around with :

Confirmation timeConfirmation time 1 Min BinSource Storage BinEA/CartonSum of Act.qty (dest)
02:27:1802:27:00CQ0505A11
02:27:3002:27:00CQ0505A11
05:32:5505:32:00CQ0505A11
10:36:3610:36:00CQ0505A11
10:36:5110:36:00CQ0505A11
12:00:4812:00:00CQ0505A11
12:23:1412:23:00CQ0505A12
12:23:3212:23:00CQ0505A11
12:40:4712:40:00CQ0505A11
12:47:0812:47:00CQ0505A11
14:03:0614:03:00CQ0505A11
14:37:3514:37:00CQ0505A11
14:39:0114:39:00CQ0505A11
14:53:2814:53:00CQ0505A11
14:56:3214:56:00CQ0505A12
15:05:2315:05:00CQ0505A11
15:35:1815:35:00CQ0505A11
15:35:2815:35:00CQ0505A11
15:35:4115:35:00CQ0505A11
15:35:5215:35:00CQ0505A11
15:36:0615:36:00CQ0505A11
1 ACCEPTED SOLUTION

rpinxt,

 

Got it. One solution you can try is:

Sum of EA/Carton =
VAR Summarized =
SUMMARIZE (
    Table1,
    Table1[Confirmation time 1 Min Bin], Table1[Source Storage Bin], Table1[EA/Carton]
)
VAR Result =
SUMX (
    Summarized,
    [EA/Carton]
)

RETURN Result

 

What this does is do the aggregation first before summing the EA/Carton column. I wasn't fully clear on what you were looking for as an end result so let me know if I'm a little off the mark. The overall concept is probably correct though.


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

View solution in original post

4 REPLIES 4
rpinxt
Impactful Individual
Impactful Individual

Thanks but that is not an option. Data is coming from our system and date/time comes in that format.

Also this is only small part of the views. In other views we would like precise timing.

rpinxt,

 

Got it. One solution you can try is:

Sum of EA/Carton =
VAR Summarized =
SUMMARIZE (
    Table1,
    Table1[Confirmation time 1 Min Bin], Table1[Source Storage Bin], Table1[EA/Carton]
)
VAR Result =
SUMX (
    Summarized,
    [EA/Carton]
)

RETURN Result

 

What this does is do the aggregation first before summing the EA/Carton column. I wasn't fully clear on what you were looking for as an end result so let me know if I'm a little off the mark. The overall concept is probably correct though.


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

rpinxt
Impactful Individual
Impactful Individual

Thanks @Wilson_ think we can work with this.

Wilson_
Solution Sage
Solution Sage

Hi rpinxt,

 

Do you ever care about the seconds in the confirmation time? Will you ever need to analyze data at the second level of granularity? If not, the easiest solution is to fix the grain of your fact table in the data source so you don't have two different lines for 02:27 in your data model in the first place


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.