Creating a measure calculation using the IF or Switch and True function

[Obiweka]

Please I would like some help to write a Measure in Power Bi using IF or Switch function to calculate carbon emission (carbon emission= Total weight x emission factor) based on data contained in two tables. The scenario is as follows:

Table 1 is contained on an excel sheet with multiple columns titled 'waste data' which i receive monthly, this sheet as shown below comes with several columns including waste description, disposal facility and total weight. The format, including headers are the same for every month so I want I or colleagues to be able to just load up and combine the sheet in a folder every month without having to modify or perform any work on it, hence I decided it was best not to implement this by implementing a conditional column. Here is a sample of the waste data excel sheet:

Date	Site Number	Site Name	Waste Description	Product Description	Total Lifts	Total Weight (kg)	Disposal Facility Type	Total Spend (£) (excl. VAT)	Total Spend (£) (incl. VAT)	Total Weight (Tonnes)
01/04/2023			Clinical Waste	SKIP	1	100	Incineration	£57.95	£69.54	0.100
07/04/2023			Cardboard	SKIP	4	400	Recycling	£231.80	£278.16	0.400
08/04/2023			Food Waste	SKIP	1	100	Anaerobic Digestion	£57.95	£69.54	0.100
010/04/2023			Glass	SKIP	1	100	Recycling	£57.95	£69.54	0.100
14/04/2023			Non-Hazardous Industrial	SKIP	1	100	Landfill	£57.95	£69.54	0.100
20/04/2023			Metal	SKIP	4	400	Incineration	£231.80	£278.16	0.400

Table 2 as shown below is a conversion factor table I created.

Waste Types	Disposal Facility	Emission Factor
Clinical Waste	Incineration	21.281
Cardboard	Recycling	21.281
Non-Hazardous Industrial	Landfill	520.335
Food Waste	Anaerobic Digestion	8.912
Glass	Recycling	21.281
Metal	Recycling	21.281

The task is to dynamically perform the following using a Measure:

IF waste type= 'value' and disposal facility= 'value' in Waste data sheet, multiply the Total weight contained in Waste data sheet by the corresponding Emission factor (Total weight x Emission factor) in the conversion factor sheet.

E.g. If for a row 1 in the Waste data sheet, waste type= 'Clinical Waste' and disposal facility= 'Incineration', multiply Total weight in Waste data sheet(0.100) by the corresponding Emission factor (21.281) in Conversion factor sheet. 

[Anonymous · Accepted solution]

Hi @Obiweka ,

You can create a calculated column as below in Table1 to get it:

Column =
VAR _efactor =
    CALCULATE (
        MAX ( 'Table2'[Emission Factor] ),
        FILTER (
            'Table2',
            'Table2'[Waste Types] = [Waste Description]
                && 'Table2'[Disposal Facility] = [Disposal Facility Type]
        )
    )
RETURN
    [Total Weight (Tonnes)] * _efactor

Best Regards

[Obiweka]

Thank you @Anonymous. The solution works. However, please I would like to know if it is also possible for this to be done as a measure as I was hoping to avoid adding an extra column because it will result to errors when I try to combine subsequent sheets. 

[Anonymous]

Hi @Obiweka ,

If you need to create a measure instead of a calculated column, you need to make a little adjustment. When you put the measure on the visual, the field [Waste Description], [Disposal Facility Type] and [Total Weight (Tonnes)] in Table1 should also be put on the visual.

Measurve =
VAR _efactor =
    CALCULATE (
        MAX ( 'Table2'[Emission Factor] ),
        FILTER (
            'Table2',
            'Table2'[Waste Types] = SELECTEDVALUE ( 'Table1'[Waste Description] )
                && 'Table2'[Disposal Facility] = SELECTEDVALUE ( 'Table1'[Disposal Facility Type] )
        )
    )
RETURN
    SELECTEDVALUE ( 'Table1'[Total Weight (Tonnes)] ) * _efactor

Best Regards