topic Re: Calculation based on text field category in DAX Commands and Tips

Calculation based on text field category

MelB_EIA — Mon, 28 Feb 2022 20:17:51 GMT

Good evening everyone

Really hoping for some help... I am trying to calculate a figure (CO2 amount) which is dependant on the category of a text filter (type of gas) and a measure (total volume of gas), and which has multiple options/values depending on the text filter.

CO2 = ( [Total volume gas seized (kg)]/1000) *

SWITCH (

Datalist[type of gas],

"Bromochloromethane", 6900,

"CFC-11", 5560,

"CFC-112", 4620,

"CFC-113", 6520,

"CFC-12", 11200,

0) )

It doesn't seem to like the fact I'm using a text filter and says it cannot find the name, even though it exists. Any ideas? Is it because it's not a numerical field? Is there another DAX expression I can use that will do the same thing?

Thanks in advance!

Re: Calculation based on text field category

Syk — Mon, 28 Feb 2022 21:16:28 GMT

If this is a calculated column, you could create an additional calculated column to look at type of gas and use it to multiply instead of using switch.

Re: Calculation based on text field category

MelB_EIA — Thu, 03 Mar 2022 17:00:53 GMT

Thanks for your response. The list of gas types is quite long, so I tried putting it in a separate table so I could create a new calculated column to do a lookup, which worked, but it is the next part I'm stuck on as it doesn't seem to recognise the calculated column in the DAX when I create a measure.

to clarify, I need to create a new field (calculated column or measure?) that calculates the CO2 using this formula:

( [Total volume gas seized]/1000) * GWP

The GWP values are in a separate table and are dependant on 'Type of gas' field.

Total volume gas seized is a measure.

Example of main incident data, with incident ID:

ID	Date	Type of gas	Reported volume	Estimated volume	Total volume
1	01/02/2022	CFC-11	12000.00	0.00	12000.00
2	02/02/2022	HCFC-502	0.00	58000.00	58000.00
2	02/02/2022	HFC-23	800.00	0.00	800.00
3	03/03/2022	HCFC-406A	542.00	0.00	542.00
4	04/02/2022	CFC-11	0.00	8795.00	8795.00
5	05/02/2022	HFC-23	0.00	7845.00	7845.00
5	05/02/2022	HFC-507	0.00	5274.00	5274.00

Example of GWP value table:

Type of gas	GWP
Bromochloromethane	6900
CFC-11	5560
CFC-112	4620
CFC-113	6520
CFC-12	11200
HCFC-502	4657
Halon 1211	1930

There are also multiple gas types seized per incident which are joined together by ID (it is a relational database).

Any ideas? Thanks!

Re: Calculation based on text field category

Anonymous — Mon, 07 Mar 2022 02:58:27 GMT

Hi MelB_EIA,

You can take a look at the following measure formula to get the result based on the current gas type and id group:

Measure = SUMX ( SUMMARIZE ( T1, [Type of gas], [ID], "total", SUM ( T1[Total volume] ) / 1000 * LOOKUPVALUE ( T2[GWP], T2[Type of gas], [Type of gas] ) ), [total] )

Regards,

Xiaoxin Sheng

Re: Calculation based on text field category

MelB_EIA — Wed, 09 Mar 2022 08:59:46 GMT

Hi Xiaoxin

Thank you for your response. I have tried this measure formula but I receive an error: " The SUM function only accepts a column reference as an argument".

"Total volume" is a calculated column - would this cause this?

Thanks

Re: Calculation based on text field category

Anonymous — Tue, 15 Mar 2022 09:04:39 GMT

Hi MelB_EIA,

The calculated column should not affect the formula calculations, I suppose they may be related to the [Type of gas] field, you can try to use the following measure formulas if helps:

Measure = SUMX ( SUMMARIZE ( T1, [Type of gas], [ID], "total", VAR currGas = SELECTEDVALUE ( T1[Type of gas] ) RETURN SUM ( T1[Total volume] ) / 1000 * LOOKUPVALUE ( T2[GWP], T2[Type of gas], currGas ) ), [total] )

If the above does not help, can you please share a pbix file with some dummy data to test?

Regards,

Xiaoxin Sheng