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.
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,
Solved! Go to Solution.
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.
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.
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
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
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.
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.
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.
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.
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
@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.
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.
@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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |