Problem with use of Calculate

DAX DAX Calculations
1

Hi,
I’m building a CO2 emissions dashboard and am having a problem converting “usage” to “emissions in kgCO2”. I’ve built a page to show my calculations. I have a Year slicer, a table showing the contents of “Conversion factors” table, two tables showing (a) “usage” (in miles per vehicle type) and (b) “Emissions in kgCO2”. I have screenshot this page (attached) to try to make it easy for you guys to visualize.
Any help gratefully appreciated.

image
image1260×651 53.8 KB

2

Hi @DJBrennan. Is your [Year] table related to your [Conversion Factors] table (i.e., Year[Year] --> ‘Conversion Factors’[Year]? If not, perhaps you could upload your work-in-progress PBIX and a mockup of the outcome you’re looking for…
Greg

3

SECR Version 5 All sitesf3d0e35c-df86-438e-86b7-95dcfc2a8990.pbix (2.4 MB)
Here’s the PBIX (I think the screenshot serves as a mockup). You’re right that there is no relationship but it appears that if I try to create one it’s “many to many cardinality” so to be avoided.

Thanks,
Denis

4

SECR Version 5 All sitesf3d0e35c-df86-438e-86b7-95dcfc2a8990.pbix (2.4 MB)

Because your “year” in the CGFactors table is actually a date field, you can link it to the date table via the date field. Then on the table view in your report, you can use the Year value from the date table instead of the value from the CGFactors table.

You will need to expand your date table to deal with 2020.

5

Let me try this. 2020, by the way is a red herring, as there’s no data.
Thx
Denid

6

Hi @DJBrennan. As Heather said, the data type mismatch between the [Year] your dates and conversion factors table is an issue. As with most things in Power BI, there are many ways to accomplish the same thing; here’s an example using a calculated column in the CGFactors table

YearText = FORMAT( YEAR( CGFactors[Year] ), "#" )

and a multiple-column LOOKUPVALUE to retrieve the appropriate conversion factor

KGCO2 Fleet Cars Diesel CY 2 = 
VAR _SelectedYear = SELECTEDVALUE( Dates[Year] ) -- this is text format in your [Dates] table
VAR _CurrentNature = "Fleet Cars"
VAR _CurrentLevel3 = "Cars average size Diesel"
VAR _DieselMiles = [Fleet Car Miles Diesel]
VAR _ConversionFactor = LOOKUPVALUE( CGFactors[Value], 
    CGFactors[YearText], _SelectedYear,
    CGFactors[Nature], _CurrentNature,
    CGFactors[Level 3], _CurrentLevel3
     )
VAR _Result = _DieselMiles * _ConversionFactor

RETURN
_Result

Is something like this what you’re looking for?

eDNA Forum - Conversion Factors - 1
eDNA Forum - Conversion Factors - 12625×1280 549 KB

Hope this helps.
Greg
eDNA Forum - Conversion Factors.pbix (2.4 MB)

7

Hi @DJBrennan, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!