Forum Discussion
Creating a measure calculation using the IF or Switch and True function
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.
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)] * _efactorBest Regards
3 Replies
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)] * _efactorBest Regards
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.
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)] ) * _efactorBest Regards
