Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good Afternoon,
Having a brain cramp on this one. I have the following Calculated Table:
= SUMMARIZECOLUMNS( STG_SamsaraEquipment_Stats_NRT[DeviceName],
STG_SamsaraEquipment_Stats_NRT[Date_Local],
"MinEngineSeconds", FIRSTNONBLANK(STG_SamsaraEquipment_Stats_NRT[ObdEngineSeconds], SUM(STG_SamsaraEquipment_Stats_NRT[ObdEngineSeconds] )),
"MaxEngineSeconds", LASTNONBLANK(STG_SamsaraEquipment_Stats_NRT[ObdEngineSeconds], SUM(STG_SamsaraEquipment_Stats_NRT[ObdEngineSeconds] )))
Which gives me this small sample dataset called "DailySummary" :
SH-18-05 | 5/23/2022 | 40350960 | 40374360 |
HSCP-2107 | 5/23/2022 | 12988080 | 13026780 |
HSCP-2005 | 5/23/2022 | 20433420 | 20449800 |
I need to add a Calculated Column called IdleDuration. This data point comes from another Fact Table called "Idle_Stats"
DeviceName | Date | IdleDuration_(M) |
SH-18-05 | 5/19/2022 | 6 |
SH-18-05 | 5/19/2022 | 7 |
SH-18-05 | 5/19/2022 | 5 |
SH-18-05 | 5/19/2022 | 4 |
HSCP-2107 | 5/19/2022 | 8 |
HSCP-2107 | 5/19/2022 | 5 |
HSCP-2107 | 5/19/2022 | 7 |
When I do a simple SUM,
= SUM(Idle_Stats[IdleDuration_(M)] ) / 60
I get:
DeviceName | Date | MinEngineSeconds | MaxEngineSeconds |
IdleDuration |
MTWH-0701 | 5/19/2022 | 15363540 | 15363540 | 4220.909575 |
SH-18-05 | 5/19/2022 | 40350960 | 40374360 | 4220.909575 |
HSCP-2107 | 5/19/2022 | 12988080 | 13026780 | 4220.909575 |
HSCP-2005 | 5/19/2022 | 20433420 | 20449800 | 4220.909575 |
I believe I am missing a Filter context or something to recognize the proper row context (DeviceName and Date).
Both Fact tables are connected to a Dim_Date table and a Dim_Device table. I am doing this via a Tabular Model in Visual Studio
All things I have tried thus far is not yielding the proper result. Any guidance would be much appreciated!
Kind Regards,
Solved! Go to Solution.
Hi @rsbin ,
When using calculated columns with summarizations you need to use the CALCULATE in order to have the row context pass to the calculation, in this case since you have the relationship with Dates and Device this should give the expected result.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @rsbin ,
When using calculated columns with summarizations you need to use the CALCULATE in order to have the row context pass to the calculation, in this case since you have the relationship with Dates and Device this should give the expected result.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix ,
Thanks for the quick response. I think that works.
The portion of my table I was looking at was giving me blanks. Just realized it was because my data feed from Source stopped over the weekend.
Just need to do some additional validation, but Thank You for confirming that I did have things set up properly.
All the Best,
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |