cancel
Showing results for
Did you mean:
Helper II

Count occurence per category for any date

Hello,

Let's suppose I have a table like this :

 call_id client_id call_type date_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
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)``````

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.

19 REPLIES 19
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)``````

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.

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 )😅

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

Best Regards,
Eyelyn Qin

Helper II

Sure @Eyelyn9  !

See the screenshot below

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:

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

Helper II

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.

Community Support

Hi @eric98 ,

Sorry, I could not get your pbix file:

Best Regards,
Eyelyn Qin

Helper II

Hi @Eyelyn9

I'm sorry, the link is expired.

You can get it here : Power BI file.

Best Regards,

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

Helper II

Hello @Eyelyn9

Below my pbix file.

You can get it with this link : Click here

Thanks for the help.

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:

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.

Helper II

Hello @Eyelyn9

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

I'm always got 0 with the flag.

Output

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.

Helper II

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

I'm getting an empty record.

Super User

@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.

Helper II

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.

Ravitaillement is order

Super User

@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.

Helper II

Yes it's showing 0.

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.

Super User

@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.

Helper II

@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
)``````

Announcements

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.

Check it Out!

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

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
Top Kudoed Authors