cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eric98
Helper II
Helper II

Count occurence per category for any date

Hello, 

Let's suppose I have a table like this : 

 

 

call_idclient_idcall_typedate_call
2472363   57147000   refund   12/04/2022
2472364   63438000   order   12/04/2022
2472365   25131000   refund   12/04/2022
2472366   61719000   order   12/04/2022
2472367   36352000   refund   12/04/2022
2472368   36352000   order   12/04/2022
2472369   29687000   refund   13/04/2022
2472370   44800000   order   13/04/2022
2472371   44800000   refund   13/04/2022

 

I want to calculate how many distinct client(s) called for a refund and an order in the same day.

For example, I have the client 36352000 who called for an refund and an order the 12/04/2022.

 

Can you help me please ?

Regards,

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @eric98 ,

 

Please modify the Flag measure:

Flag = 
    var _combine = 
        CONCATENATEX(
        FILTER(
            ALLSELECTED(CHIAMATE),
            CHIAMATE[CHIA_CLI] = MAX(CHIAMATE[CHIA_CLI]) && 
            [CHIA_DAT]>= CALCULATE(MIN('CHIAMATE'[CHIA_DAT]),ALLSELECTED(CHIAMATE)) &&
            [CHIA_DAT]<= CALCULATE(MAX('CHIAMATE'[CHIA_DAT]),ALLSELECTED(CHIAMATE))
        ),
        CHIAMATE[CHIA_TIP], ",")
    
return 
    IF(CONTAINSSTRING(_combine, "R") && CONTAINSSTRING(_combine, "G"), 1,0)

Eyelyn9_0-1651136815949.png

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

19 REPLIES 19
Eyelyn9
Community Support
Community Support

Hi @eric98 ,

 

Please modify the Flag measure:

Flag = 
    var _combine = 
        CONCATENATEX(
        FILTER(
            ALLSELECTED(CHIAMATE),
            CHIAMATE[CHIA_CLI] = MAX(CHIAMATE[CHIA_CLI]) && 
            [CHIA_DAT]>= CALCULATE(MIN('CHIAMATE'[CHIA_DAT]),ALLSELECTED(CHIAMATE)) &&
            [CHIA_DAT]<= CALCULATE(MAX('CHIAMATE'[CHIA_DAT]),ALLSELECTED(CHIAMATE))
        ),
        CHIAMATE[CHIA_TIP], ",")
    
return 
    IF(CONTAINSSTRING(_combine, "R") && CONTAINSSTRING(_combine, "G"), 1,0)

Eyelyn9_0-1651136815949.png

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Eyelyn9
Community Support
Community Support

Hi @eric98 ,

 

But you can see that for on day, a [CHIA_CLI] (client) can call for G and for R [CHIA_TIP]

 

Sorry I just could not find such data(both R and G for the same Client on 2022/4/14 )😅

Eyelyn9_0-1651051601362.png

Could you please help to point out where such data is? By highlight it on your screenshots? 

 

Best Regards,
Eyelyn Qin

Sure @Eyelyn9  !

See the screenshot below

 

eric98_0-1651052238360.png

 

Eyelyn9
Community Support
Community Support

Hi @eric98 ,

 

Based on my test , there is no match in your data—— both R and G in column [CHIA_TIP] of each [CHIA_CLI] and each [CHIA_DAT]. So the flag is always 0 .

 

See the test visual:

Eyelyn9_0-1651049991751.png

For each [CHIA_CLI] and each [CHIA_DAT], there is only one different [CHIA_TIP], which is not met the basic condition of my flag measure.

 

Best Regards,
Eyelyn Qin

But you can see that for on day, a [CHIA_CLI] (client) can call for G and for R [CHIA_TIP]

I don't know how to count that correctly.

Eyelyn9
Community Support
Community Support

Hi @eric98 ,

 

Sorry, I could not get your pbix file:

Eyelyn9_0-1651043694585.png

Best Regards,
Eyelyn Qin

Hi @Eyelyn9 

I'm sorry, the link is expired.

You can get it here : Power BI file. 

Best Regards,

Eyelyn9
Community Support
Community Support

Hi @eric98 ,

 

If so ,could you please share me with your pbix file after removing sensitive data to clarify your scenario?

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

 

 

Best Regards,
Eyelyn Qin

Hello @Eyelyn9 

Below my pbix file.

You can get it with this link : Click here 

Thanks for the help.

Eyelyn9
Community Support
Community Support

Hi @eric98 ,

 

According to your description, you want to know how many client_ids who orders and refunds at a same day.

Please try to create a flag measure for visual-level filter later:

Flag = 
var _combine=CONCATENATEX(FILTER(ALLSELECTED('Table'),[client_id]=MAX('Table'[client_id]) && [date_call]=MAX('Table'[date_call])),[call_type],",")
return IF(CONTAINSSTRING(_combine,"order") && CONTAINSSTRING(_combine,"refund"),1,0)

Then calcaulate the distinct count:

Distinct Count = CALCULATE(DISTINCTCOUNT('Table'[client_id]),FILTER(ALLSELECTED('Table'),[Flag]=1))

Output:

Eyelyn9_0-1650610522267.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @Eyelyn9 

I don't know why but it is not working.

I'm always got 0 with the flag.

 

Output

 

eric98_0-1650618612625.png

 

Samarth_18
Super User
Super User

Hi @eric98 ,

 

You could create a column as below:-

Column = 
var result = COUNTROWS(FILTER('Table (4)','Table (4)'[client_id] = EARLIER('Table (4)'[client_id]) && 'Table (4)'[date_call] = EARLIER('Table (4)'[date_call]) && trim('Table (4)'[call_type]) in {"refund","order"}))
return IF(result >= 2,1,0)

use it as filter on your visual or create a measure below:-

Measure 2 = 
calculate(DISTINCTCOUNT('Table (4)'[client_id]),'Table (4)'[Column] = 1)

 

Thanks,

Samarth

 


If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin


I've tried your formula but i'm not getting the good result.

I'm getting an empty record.

@eric98 Could you please elaborate with examples like in what cases its failing?


If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin


When I make the calculated column, I've got a lot of result with 0 but only few with 1.

With the measure, i've got an empty record in the card visual.

The filter work with the order filter but not for both order and refund.

eric98_0-1650366102643.png
eric98_1-1650366143196.png

Ravitaillement is order

@eric98 Actually based on question we need to consider only those records which is having order and refund on the same day. I feel you must be having more 0's than 1.

So with this condition can check what are the records which is having same day refund & order and still its showing 0 or blank?


If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin


Yes it's showing 0.

eric98_0-1650367210292.png

Note :  Ravitaillement is order, Panne is refund

For the client 54273000, in the same day 13/04/2022, I have an order and a refund, but the column1 show 0.

 

@eric98 Could you please share your implemented formula?


If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin


@Samarth_18yeah sure,

This is the column :

Column1 = 
    var result = 
    COUNTROWS(
        FILTER(
            CHIAMATE,
            CHIAMATE[CHIA_CLI] = EARLIER(CHIAMATE[CHIA_CLI]) &&
            CHIAMATE[CHIA_DAT] = EARLIER(CHIAMATE[CHIA_DAT]) &&
            TRIM(CHIAMATE[CHIA_TIP]) in {"G", "R"}
        )
    )
    return IF(result>=2,1,0)

 

Note :

CHIA_CLI : customer_id

CHIA_DAT : call_date

CHIA_TIP : call_type (R for order and G for refund)

 

This is the measure : 

Num Of Clients = 
    CALCULATE(
        DISTINCTCOUNT(CHIAMATE[CHIA_CLI]),
        CHIAMATE[Column1] = 1
    )

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors