Conditional measure producing blanks when based on other measures, need to give a value

[ofoster]

Hello,

I'm trying to build a conditional model. In this model, I am trying to output a measure (in this case one of 6 pre-calculated measures) based on conditions that can be changed via two slicers. There is no connection between the slicers, so they are influencing each other (they do not connect in the model view and both are set to no interactions with anything in this BI except this one card).

I have checked my intermediate steps to make sure that they are not blank. Everything works on it's own until I combine them together. I've had a coworker check those intermediate steps and they calculate correctly with a General format (we've tried converting them to different data types, including text, with no success).

Here is my measure calculation:

% Diff LH W/WO Fuel/LB = IF(OR(AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2020, SELECTEDVALUE('Shippy'[Shippy Date].[Year])= 2021), AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2021, SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2020)), 'Order Info'[20-21 Diff LH w/wo fuel/lb],

    IF(OR(AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2020 , SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2022) , AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2022,SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2020)), 'Order Info'[20-22 Diff LH w/wo fuel/lb],

        IF(OR(AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2020 ,SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2023) , AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2023,  SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2020)), 'Order Info'[20-23 Diff LH w/wo fuel/lb],

            IF(OR(AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2021 ,SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2022) , AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2022 , SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2021)), 'Order Info'[21- 22 Diff LH w/wo fuel/lb],

                IF(OR(AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2021 ,SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2023) , AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2023 , SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2021)), 'Order Info'[21- 23 Diff LH w/wo fuel/lb],

                    IF(OR(AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2022, SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2023) , AND(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = 2023, SELECTEDVALUE('Shippy'[Shippy Date].[Year]) = 2022)), 'Order Info'[22- 23 Diff LH w/wo fuel/lb],

                    IF(SELECTEDVALUE('Ship Date'[Ship Date].[Year]) = SELECTEDVALUE('Shippy'[Shippy Date].[Year]), "No Change",

                        0)))))))

Picture for clarity:

Here is what we get with the following conditions:

Any combination of unequal years give us this Blank. So for example for 21 vs 22 it needs to produce either a 0.3 or 30%.

When the years match, we get the correct output:

 And anything else produces what we want (I will be changing this to Blank at a later date, this is just so I can differentiate different circumstances):

Thank you for your help.

EDIT (5/22/23):

Below is a sample dataset called Order Info.

Order ID	Ship Date	Weight	Linehaul Charges	Total Fuel Surcharges
1	3/12/2020	230	105.5	14.46
2	4/16/2020	230	101.2	33.45
3	6/28/2020	109	111.5	12.18
4	7/6/2020	1919	236.17	197.12
5	12/4/2020	2650	750	18.87
6	3/12/2021	2128	750	118.4
7	4/16/2021	1992	171.97	33.36
8	6/28/2021	1685	263.97	22.55
9	7/6/2021	1992	602.35	36.06
10	12/4/2021	14160	888.25	34
11	3/12/2022	800	241.94	19.77
12	4/16/2022	14332	945.62	23.22
13	6/28/2022	109	90.1	13.7
14	7/6/2022	28000	2314.26	33.65
15	12/4/2022	1521	237.85	74.66
16	3/12/2023	29177	1775.5	63.11
17	4/16/2023	5024	560.13	58.31
18	6/28/2023	690	804.91	5.76
19	7/6/2023	1432	457.5	122.61
20	12/4/2023	916	750	577.02

For some of the formuli, they reference a selected value found in Net Fuel:

Charge Analysis

LH

LH + Fuel

Here are some of the other referenced formulas (NOTE: some of the measures below are repeated several times in the above formula with small alterations, so I'll only be writing it down once for the main structure. I'll mark the repeated and altered measures with a ** at the beginning):

Calculated Columns:

LH + Fuel = 'Order Info'[Linehaul Charges] + 'Order Info'[Total Fuel Surcharges]

Measures:

LH W/WO Fuel = IF(SELECTEDVALUE('Net Fuel'[Charge Analysis]) = "LH", SUM('Order Info'[Linehaul Charges]), SUM('Order Info'[LH + Fuel]))

LH W/WO Fuel/LB = DIVIDE('Order Info'[LH W/WO Fuel], SUM('Order Info'[Weight]))

** 2020 LH W/WO Fuel/LB = CALCULATE(DIVIDE('Order Info'[LH W/WO Fuel], SUM('Order Info'[Weight])), FILTER('Ship Date', 'Ship Date'[Ship Date].[Year] = 2020))

** 20-21 Diff LH w/wo fuel/lb = ('Order Info'[2021 LH W/WO Fuel/LB] - 'Order Info'[2020 LH W/WO Fuel/LB])/'Order Info'[2020 LH W/WO Fuel/LB]

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523