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.
Hi all,
I have a measure that allows me to calculate the number of calls within a year following three conditions :
- [CallType] = C
- [Durée]<=30 AND <120 (seconds)
- Distinct [Clip] (phone number) per month : if I have 2 identic [Clip] within a month it counts only once, but if the same [Clip] appears in two different months we count it once in each month) + all [Clip]=BLANK()
CALLS < 2Min =
SUMX(
VALUES(CallHistory[Année-mois]),
CALCULATE(
DISTINCTCOUNTNOBLANK(CallHistory[Clip])
+ CALCULATE(COUNTROWS(CallHistory), CallHistory[Clip]=BLANK()),
CallHistory[CallType]="C",
FiltreAppels[Durée]>=30,
FiltreAppels[Durée]<120
)
)
I need to create a column such as IF(same rules as the measure), "1", "0").
In other words, I need a column that :
IF (AND(CallHistory[CallType]="C",
AND(FiltreAppels[Durée]>=30,
AND(FiltreAppels[Durée]<120,
OR(CallHistory[Clip]=BLANK(), [Clip.... ??]
[Clip.... ??] = first appearence of this Clip within the month
Any idea on how do I write that?
Thanks in advance,
Ana
Solved! Go to Solution.
Hi,
Share data in a format that can be pasted in an MS Excel file.
Hi,
Write these calculated column formulas
Month of call = MONTH(Data[StartOfCall])
Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Clip]=EARLIER(Data[Clip])&&Data[CallType]="C"&&Data[Durée]<=120&&Data[StartOfCall]<=EARLIER(Data[StartOfCall])&&Data[Month of call]=EARLIER(Data[Month of call])))=1,1,BLANK())
Hope this helps.
Hello,
I need to add a new condition to this calculation : if the number ([Clip]) is already flagged as "Reactif" within the month, I should not count it. In other words, to my column "CALLS < 2 min"
CALLS < 2Min = if(CALCULATE(COUNTROWS(Data), FILTER(
Data,Data[Clip]=EARLIER(Data[Clip])&&
Data[CallType]="C"&&
Data[Durée]<=120&&
Data[StartOfCall]<=EARLIER(Data[StartOfCall])&&
Data[Month of call]=EARLIER(Data[Month of call])))=1,1,BLANK())
I need to add the condition "if it's flagged Reactif whitin the month then BLANK()
Here's a sample of the data
@Ashish_Mathur any inspiration on how to do this?
Hi,
That download link opens up an error page.
Hi, could you please try this link : https://we.tl/t-XeMJWDtxqy?utm_campaign=TRN_TDL_05&utm_source=sendgrid&utm_medium=email&trk=TRN_TDL_...
Thank you in advance!
Hi,
Try this calculated column formula
CALLS < 2Min = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Clip]=EARLIER(Data[Clip])&&Data[Yearmonth]=EARLIER(Data[Yearmonth])))>0,BLANK(),if(CALCULATE(COUNTROWS(Data), FILTER(
Data,Data[Clip]=EARLIER(Data[Clip])&&
Data[CallType]="C"&&
Data[Durée]<=120&&
Data[StartOfCall]<=EARLIER(Data[StartOfCall])&&
Data[Month of call]=EARLIER(Data[Month of call])))=1,1,BLANK()))
Hope this helps.
Thank you so much!
It works nicely, I only made one adaptation : instead of "Month of call" I used "YearMonth of call".
You are welcome.
Hi,
Write these calculated column formulas
Month of call = MONTH(Data[StartOfCall])
Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Clip]=EARLIER(Data[Clip])&&Data[CallType]="C"&&Data[Durée]<=120&&Data[StartOfCall]<=EARLIER(Data[StartOfCall])&&Data[Month of call]=EARLIER(Data[Month of call])))=1,1,BLANK())
Hope this helps.
Hi,
Share some data and show the expected result. Do you want a measure solution?
Hi Ashish,
thank you for your suggestion : )
heres a table of an example. The yellow column is the expected result and the 'reason' column explains why it counts as 1 or BLANK.
Hi,
Share data in a format that can be pasted in an MS Excel file.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |