Assistance with Filter not working as expected

[EZimmet]

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 ))

    )

[v-nuoc-msft]

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.

[EZimmet]

Same Results the two lines with the Red X still showing

[EZimmet · Accepted solution]

I ended up adding more filters to the calculations to seperate the results them combined the results into the total section