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
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
v-eqin-msft
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
v-eqin-msft
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.

v-eqin-msft
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 @v-eqin-msft  !

See the screenshot below

 

eric98_0-1651052238360.png

 

v-eqin-msft
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.

v-eqin-msft
Community Support
Community Support

Hi @eric98 ,

 

Sorry, I could not get your pbix file:

Eyelyn9_0-1651043694585.png

Best Regards,
Eyelyn Qin

Hi @v-eqin-msft 

I'm sorry, the link is expired.

You can get it here : Power BI file. 

Best Regards,

v-eqin-msft
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 @v-eqin-msft 

Below my pbix file.

You can get it with this link : Click here 

Thanks for the help.

v-eqin-msft
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 @v-eqin-msft 

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
Community Champion
Community Champion

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

 

Best Regards,
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?

Best Regards,
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

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?

Best Regards,
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

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?

Best Regards,
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

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