Forum Discussion
Resolver IAssistance with Filter not working as expected
TY for assisting with this one,
Not sure where my filter is failing but here goes:
- I Filter on Column A, E
- I have a multiple Clients this one is at 4 locations
- 3 of the locations I need to do a Calculation for Emissions & MWh
- 1 Location in the example below [ Site 4 ]No Calculation I just need to pull the Raw numbers for MT CO2e & MWh from the input file
Input File:
| A | B | C | D | E | F |
| Cal / No Calc | Date | Client | Site | Category | Value |
| Calc | 8/1/2023 | ABC | Site 1 | Emissions | 600 |
| Calc | 8/1/2023 | ABC | Site 1 | MWh | 1707 |
| Calc | 8/1/2023 | ABC | Site 2 | Emissions | 636 |
| Calc | 8/1/2023 | ABC | Site 2 | MWh | 2119 |
| Calc | 8/1/2023 | ABC | Site 3 | Emissions | 691 |
| Calc | 8/1/2023 | ABC | Site 3 | MWh | 1314 |
| No Cal | 8/1/2023 | ABC | Site 4 | MT CO2e | 113 |
| No Cal | 8/1/2023 | ABC | Site 4 | MWh | 214 |
| Calc | 9/1/2023 | ABC | Site 1 | Emissions | 522 |
| Calc | 9/1/2023 | ABC | Site 1 | MWh | 1629 |
| Calc | 9/1/2023 | ABC | Site 2 | Emissions | 522 |
| Calc | 9/1/2023 | ABC | Site 2 | MWh | 2033 |
| Calc | 9/1/2023 | ABC | Site 3 | Emissions | 565 |
| Calc | 9/1/2023 | ABC | Site 3 | MWh | 1294 |
| No Cal | 9/1/2023 | ABC | Site 4 | MT CO2e | 90 |
| No Cal | 9/1/2023 | ABC | Site 4 | MWh | 206 |
Current CODE:
_Numbers =
// Pulls from Live DB
// Good Index 4 --------------------------------------------------------------------------------------------------
VAR _rack =
COUNTROWS ( FILTER (( '__SQL Query' ),'__SQL Query'[EQType] = "Rack") )
// Good Index 5 --------------------------------------------------------------------------------------------------
VAR _mainframe =
COUNTROWS ( FILTER (( '__SQL Query' ),'__SQL Query'[EQType] = "Mainframe") )
// Good Index 6 --------------------------------------------------------------------------------------------------
VAR _storage_frame =
COUNTROWS ( FILTER (( '__SQL Query' ),'__SQL Query'[EQType] = "Storage Frame") )
// Good Index 7 -------------------------------------------------------------------------------------------------- VAR _storage_frame_mainframe =
COUNTROWS ( FILTER (( '__SQL Query' ),'__SQL Query'[EQType] = "Storage Frame - Mainframe") )
// Filter for each Value from Input File
// Index 9 --------------------------------------------------------------------------------------------------------------------
VAR _Cal_MWh =
CALCULATE(
SUM('Client Report'[Value] ),
FILTER (( 'Client Report' ), 'Client Report'[Cal / No Calc] = "Calc") ,
FILTER (( 'Client Report' ), 'Client Report'[Category] = "MWh") )
// Index 10 -------------------------------------------------------------------------------------------------------------------
VAR _Cal_Emissions =
CALCULATE(
SUM('Client Report'[Value] ),
FILTER (( 'Client Report' ), 'Client Report'[Cal / No Calc] = "Calc") ,
FILTER (( 'Client Report' ), 'Client Report'[Category] = "Emissions") )
// Index 11 ------------------------------------------------------------------------------------------------------------------
VAR _No_Cal_MWh =
CALCULATE(
SUM('Client Report'[Value] ),
FILTER (( 'Client Report' ), 'Client Report'[Cal / No Calc] = "No Cal") ,
FILTER (( 'Client Report' ), 'Client Report'[Category] = "MWh") )
// Index 12 -------------------------------------------------------------------------------------------------------------------
VAR _No_Cal_MT_CO2e =
CALCULATE(
SUM('Client Report'[Value] ),
FILTER (( 'Client Report' ), 'Client Report'[Cal / No Calc] = "No Cal") ,
FILTER (( 'Client Report' ), 'Client Report'[Category] = "MT CO2e") )
//------------------------------------------------------------------------------------------------------------------------------
RETURN
SWITCH (
TRUE (),
//– Value from Index 11
MAX ( '_Output'[Expected] ) = "Total Electricity usage for month MWh_nc", (_No_Cal_MWh ),
//– Value from Index 12
MAX ( '_Output'[Expected] ) = "Total Carbon Emission for month Co2e_nc", (_No_Cal_MT_CO2e ),
//– Value from Index 9
MAX ( '_Output'[Expected] ) = "Total Electricity usage for month MWh", ((( _rack + _mainframe + _storage_frame + _storage_frame_mainframe ) * (1.5 * 730 * 1.75 / 1000) + _Cal_MWh )),
//– Value from Index 10
MAX ( '_Output'[Expected] ) = "Total Carbon Emission for month Co2e", ((( _rack + _mainframe + _storage_frame + _storage_frame_mainframe ) * (1.5 * 730 * 1.75 / 1000) + _Cal_Emissions ))
)
I ended up adding more filters to the calculations to seperate the results them combined the results into the total section
3 Replies
Hi EZimmet
Here I suggest you try the following actions:
First, make sure that the correct table relationships are established.
Instead of using MAX to get a single value from a column, consider using the SELECTEDVALUE function.
I made the following modifications to your code:
_Numbers = // Pulls from Live DB // Good Index 4 -------------------------------------------------------------------------------------------------- VAR _rack = COUNTROWS ( FILTER (( '__SQL Query' ),'__SQL Query'[EQType] = "Rack") ) // Good Index 5 -------------------------------------------------------------------------------------------------- VAR _mainframe = COUNTROWS ( FILTER (( '__SQL Query' ),'__SQL Query'[EQType] = "Mainframe") ) // Good Index 6 -------------------------------------------------------------------------------------------------- VAR _storage_frame = COUNTROWS ( FILTER (( '__SQL Query' ),'__SQL Query'[EQType] = "Storage Frame") ) // Good Index 7 -------------------------------------------------------------------------------------------------- VAR _storage_frame_mainframe = COUNTROWS ( FILTER (( '__SQL Query' ),'__SQL Query'[EQType] = "Storage Frame - Mainframe") ) // Filter for each Value from Input File // Index 9 -------------------------------------------------------------------------------------------------------------------- VAR _Cal_MWh = CALCULATE( SUM('Client Report'[Value] ), 'Client Report'[Cal / No Calc] = "Calc" , 'Client Report'[Category] = "MWh") // Inde VAR _Cal_Emissions = CALCULATE( SUM('Client Report'[Value] ), 'Client Report'[Cal / No Calc] = "Calc" , 'Client Report'[Category] = "Emissions") // Index 11 ------------------------------------------------------------------------------------------------------------------ VAR _No_Cal_MWh = CALCULATE( SUM('Client Report'[Value] ), 'Client Report'[Cal / No Calc] = "No Cal" , 'Client Report'[Category] = "MWh") // Index 12 ------------------------------------------------------------------------------------------------------------------- VAR _No_Cal_MT_CO2e = CALCULATE( SUM('Client Report'[Value] ), 'Client Report'[Cal / No Calc] = "No Cal" , 'Client Report'[Category] = "MT CO2e") //------------------------------------------------------------------------------------------------------------------------------ RETURN SWITCH ( TRUE (), //– Value from Index 11 SELECTEDVALUE( '_Output'[Expected] ) = "Total Electricity usage for month MWh_nc", _No_Cal_MWh , //– Value from Index 12 SELECTEDVALUE( '_Output'[Expected] ) = "Total Carbon Emission for month Co2e_nc", _No_Cal_MT_CO2e , //– Value from Index 9 SELECTEDVALUE( '_Output'[Expected] ) = "Total Electricity usage for month MWh", ( _rack + _mainframe + _storage_frame + _storage_frame_mainframe ) * (1.5 * 730 * 1.75 / 1000) + _Cal_MWh , //– Value from Index 10 SELECTEDVALUE( '_Output'[Expected] ) = "Total Carbon Emission for month Co2e", ( _rack + _mainframe + _storage_frame + _storage_frame_mainframe ) * (1.5 * 730 * 1.75 / 1000) + _Cal_Emissions )Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
