Forum Discussion
Comparing multiple dates with multiple tables
Hi ajbogle,
Base on the input you gave the following approach should to it. But since you say there are more requriements I do not know whether it will cover it end-2-end. 🙂
Please find below my example data:
Operations Table:
Record Table:
Please be aware that the column FYI is only for your information about which call is in time, before or after. It will not be used in the further calculation.
Number Table (Dimension):
Data Model:
Measure:
Calls In Time = SUMX( VALUES(Dim_TelephoneNumber[PK_TelNumber]), var var_StartTime = CALCULATE(MAX(Operations[Start Date])) var var_EndTime = CALCULATE(MAX(Operations[End Date])) var var_CallTime = CALCULATE(MAX('Call Records'[call_date_started])) RETURN IF(var_CallTime >= var_StartTime && var_CallTime <= var_EndTime, 1,0) )Result:
with 3 calls in time. The SUMX makes sure that you have the right total.
Additional Comment:
When there are further attributes like call type or something else which change the granularity it might be nexessary to do adjustments in the measure. But absed on your input and the understanding I got from it this should do the job or lead you to the result. 🙂
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hi ajbogle,
Base on the input you gave the following approach should to it. But since you say there are more requriements I do not know whether it will cover it end-2-end. 🙂
Please find below my example data:
Operations Table:
Record Table:
Please be aware that the column FYI is only for your information about which call is in time, before or after. It will not be used in the further calculation.
Number Table (Dimension):
Data Model:
Measure:
Calls In Time =
SUMX(
VALUES(Dim_TelephoneNumber[PK_TelNumber]),
var var_StartTime = CALCULATE(MAX(Operations[Start Date]))
var var_EndTime = CALCULATE(MAX(Operations[End Date]))
var var_CallTime = CALCULATE(MAX('Call Records'[call_date_started]))
RETURN
IF(var_CallTime >= var_StartTime && var_CallTime <= var_EndTime, 1,0)
)
Result:
with 3 calls in time. The SUMX makes sure that you have the right total.
Additional Comment:
When there are further attributes like call type or something else which change the granularity it might be nexessary to do adjustments in the measure. But absed on your input and the understanding I got from it this should do the job or lead you to the result. 🙂
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Brilliant! This work perfectly, I had the seperate Phone table but I never thought to reference both the Ops/Call record table.
Cheers!
Hi ajbogle ,
Awesome and thanky you for your feedback! 🙂 This is one of the gret benefits of using a starschema model (Understand star schema and the importance for Power BI - Power BI | Microsoft Learn). Your oeprations and record tables are fact tables and the phone table is a dimension tables. You can connect multiple fact tables to your dimension tables using a key which enebales you to do cross-tables calculation for this key. It is also common to have multiple dimensions tables connectiing to mutliple fact tables which enables more detailed cross-table analysis.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.