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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kukusiki83
Frequent Visitor

Count Values with condition

Hello, I need help please!

 

I have a table

SaleRepName  - ItemNum – SaleDate - SaleTime

 

I need to count the number of sales per SaleRep, BUT

all the sales made by the same SaleRep within 2.5 minutes period must be counted as 1

 

Ex.:

 

Alex        12345    2022-11-20  15:15:01

Alex        12345    2022-11-20  15:17:01

Alex        12345    2022-11-20  15:25:01

Max         54321   2022-11-20  15:30:05

Max         54321   2022-11-20  15:32:05

BOB         12555   20222-11-21 16:21:05   

 

Expected result:  Alex – 2 Sales, Max – 1 Sale, Bob – 1Sale

 

Thank you,  

5 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Kukusiki83 , Try a new column  like

 

 

Var _datetime =[SaleDate] - [SaleTime]

var _max =  maxx(filter(Table, Table[SalesRepName] = earlier([SalesRepName]) && ([SaleDate] - [SaleTime]) < [SaleDate] - [SaleTime]) , [SaleDate] - [SaleTime])

return

if( datediff(_max,_datetime ,second) <150, 0,1)

 

and you can sum this column

View solution in original post

Mikelytics
Resident Rockstar
Resident Rockstar

Hi @Kukusiki83 

 

Please try the following approach using a measure. 

Mikelytics_0-1669325908562.png

 

Sales Made by Sales Rep = 

SUMX(
    VALUES('Sample'[Sales Rep]),
        CALCULATE(
            SUMX(
                VALUES('Sample'[Date-Time]),
                var var_CurrentTime = [Date-Time]
                var var_TimeBefore = CALCULATE(MAX('Sample'[Date-Time]),'Sample'[Date-Time] < var_CurrentTime) + 0
                RETURN
                IF( DATEDIFF(var_TimeBefore,var_CurrentTime,SECOND) <150, 0,1)
            )
        )
)

 

Please before you start creating the Measure make sure that you combine the date and the time column. I did it upfront in Power Query like this. There might be easier ways but I focussed on solving the emasure problem. 🙂

 

Get combined Date-Time:

Base

Mikelytics_1-1669326051655.png

Transform columns to decimal:

Mikelytics_2-1669326082937.png

Combine columns with custom function

Mikelytics_3-1669326107214.png

Change type of new column to date time

Mikelytics_4-1669326147418.png

Remove date and time column 

Mikelytics_5-1669326167462.png

 

Base on this table please use the DAX measure on in the beginning of the post 

 

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

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

LinkedIn

 

 

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

Hi @Kukusiki83 

 

Can you please try:

 

Sales Made by Sales Rep = 

SUMX(
    SUMMARIZE(
        'Sample',
        'Sample'[Sales Rep],
        'Sample'[Sales Item]
    ),
        CALCULATE(
            SUMX(
                VALUES('Sample'[Date-Time]),
                var var_CurrentTime = [Date-Time]
                var var_TimeBefore = CALCULATE(MAX('Sample'[Date-Time]),'Sample'[Date-Time] < var_CurrentTime) + 0
                RETURN
                IF( DATEDIFF(var_TimeBefore,var_CurrentTime,SECOND) <150, 0,1)
            )
        )
)

 

Mikelytics_1-1669383621585.png

 

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

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

LinkedIn

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

you're THE BEST!!!

Thank you!!!

View solution in original post

Hi @Kukusiki83 

 

I might have a solution. If you have performance issues somteimes its better to use a calculated column. So luckkily I still saved the sample data and measure for your case.

 

Can you please try the following approach?

 

1) Create in your table the followin calculated column:

Mikelytics_0-1669661967022.png

Calculated Column - Sales COunter = 

var var_CurrentSalesRep = [Sales Rep]
var var_CurrentSalesTime = [Date-Time]
var var_SalesTimeTreshHold = [Date-Time] - TIME(0,0,150)

var var_Sales_Previos_150_Secs =
COUNTROWS(
   FILTER(
      'Sample',
      [Sales Rep] = var_CurrentSalesRep &&
      [Date-Time] < var_CurrentSalesTime &&
      [Date-Time] >= var_SalesTimeTreshHold
   )
)

RETURN
IF(var_Sales_Previos_150_Secs>0,0,1)

 

Put the column in your matrix (with sum) or create a sum emasure on top (the yellow one is the new calculated column):

Mikelytics_2-1669662052583.png

 

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

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

LinkedIn

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

11 REPLIES 11
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @Kukusiki83 

 

Please try the following approach using a measure. 

Mikelytics_0-1669325908562.png

 

Sales Made by Sales Rep = 

SUMX(
    VALUES('Sample'[Sales Rep]),
        CALCULATE(
            SUMX(
                VALUES('Sample'[Date-Time]),
                var var_CurrentTime = [Date-Time]
                var var_TimeBefore = CALCULATE(MAX('Sample'[Date-Time]),'Sample'[Date-Time] < var_CurrentTime) + 0
                RETURN
                IF( DATEDIFF(var_TimeBefore,var_CurrentTime,SECOND) <150, 0,1)
            )
        )
)

 

Please before you start creating the Measure make sure that you combine the date and the time column. I did it upfront in Power Query like this. There might be easier ways but I focussed on solving the emasure problem. 🙂

 

Get combined Date-Time:

Base

Mikelytics_1-1669326051655.png

Transform columns to decimal:

Mikelytics_2-1669326082937.png

Combine columns with custom function

Mikelytics_3-1669326107214.png

Change type of new column to date time

Mikelytics_4-1669326147418.png

Remove date and time column 

Mikelytics_5-1669326167462.png

 

Base on this table please use the DAX measure on in the beginning of the post 

 

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

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

LinkedIn

 

 

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi. it worrks perfect. thank you so much. 

I just was wondering, how can I add onother condition on top of it: 

 

all the sales made by the same SaleRep within 2.5 minutes period must be counted as 1, only if the item number is the same.

 

Thank you!!!

Hi @Kukusiki83 

 

Can you please try:

 

Sales Made by Sales Rep = 

SUMX(
    SUMMARIZE(
        'Sample',
        'Sample'[Sales Rep],
        'Sample'[Sales Item]
    ),
        CALCULATE(
            SUMX(
                VALUES('Sample'[Date-Time]),
                var var_CurrentTime = [Date-Time]
                var var_TimeBefore = CALCULATE(MAX('Sample'[Date-Time]),'Sample'[Date-Time] < var_CurrentTime) + 0
                RETURN
                IF( DATEDIFF(var_TimeBefore,var_CurrentTime,SECOND) <150, 0,1)
            )
        )
)

 

Mikelytics_1-1669383621585.png

 

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

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

LinkedIn

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi. I hope u'll be able to help me again. 

The DAX that u gave me works perfectly with a small sample of data. when I try to make it work with my real data (40k+ lines) it just freezes...

 

Is there a way to simplify it somehow?

 

My data is in excel, so would it help to make the calculation directly in excel and uploaod it to PowerBI? If so, how? 

Thanks 

Hi @Kukusiki83 

 

I might have a solution. If you have performance issues somteimes its better to use a calculated column. So luckkily I still saved the sample data and measure for your case.

 

Can you please try the following approach?

 

1) Create in your table the followin calculated column:

Mikelytics_0-1669661967022.png

Calculated Column - Sales COunter = 

var var_CurrentSalesRep = [Sales Rep]
var var_CurrentSalesTime = [Date-Time]
var var_SalesTimeTreshHold = [Date-Time] - TIME(0,0,150)

var var_Sales_Previos_150_Secs =
COUNTROWS(
   FILTER(
      'Sample',
      [Sales Rep] = var_CurrentSalesRep &&
      [Date-Time] < var_CurrentSalesTime &&
      [Date-Time] >= var_SalesTimeTreshHold
   )
)

RETURN
IF(var_Sales_Previos_150_Secs>0,0,1)

 

Put the column in your matrix (with sum) or create a sum emasure on top (the yellow one is the new calculated column):

Mikelytics_2-1669662052583.png

 

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

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

LinkedIn

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

thanks again, it worked better with a bigger set of data. 

@Kukusiki83  small disclaimer to the new approach. Since we use a calculated column it could be that the reoprt refresh takes a little bit longer. But in the report view and when the user interacts with the report it should be much fast because the core claculation is done upfront.

 

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

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

LinkedIn

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

you're THE BEST!!!

Thank you!!!

@Kukusiki83 

 

Thank you for your feedback! 🙂

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
amitchandak
Super User
Super User

@Kukusiki83 , Try a new column  like

 

 

Var _datetime =[SaleDate] - [SaleTime]

var _max =  maxx(filter(Table, Table[SalesRepName] = earlier([SalesRepName]) && ([SaleDate] - [SaleTime]) < [SaleDate] - [SaleTime]) , [SaleDate] - [SaleTime])

return

if( datediff(_max,_datetime ,second) <150, 0,1)

 

and you can sum this column

it doesn't seem to work (unless I'm doing something wrong) I get a column with 0 only

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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