Calculated column based on lookup table returns empty

[Anonymous]

I'm trying to calculate for every contract in a dataset with contracts whether or not a specific budget applies or not. This budget I need to detract from my overall figure, so I need to have it on a contract to contract basis.

I have queried a simple lookup table where I have all the relevant filter columns (product, model, duration, ...) and how high the specific budget is for these conditions. I want to calculate a column with for each contract the applied budget. I'll enclose my current DAX lines for calculating this column, and it does not give me syntax errors, but the resulting column is completely empty.

My main table is 'Retail+Fleet' and 'Special actions' is the small lookup table with the conditions and the [Budget action] column with the size of the specific budget. I've also included a simplified version of the two tables, where the green column indicates what I expected the resulting column to look like, but that at the moment is completely empty (not even showing null or 0). Does anyone have an idea where I'm making a mistake?

SpecialAction = 
    /* Use if lookup doesn't bring anything back - this is for easiness just set to 0 */
        VAR DefaultAction = 0

    /* Product Class (to filter on PL) */
        VAR ProductPL = 'Retail+Fleet'[Product Class]
    
    /* New Car */
        VAR NewCar = 'Retail+Fleet'[New/Used car]

    /* Duration */
        VAR Duration = 'Retail+Fleet'[Contract duration]

    /* Calculation Date */
        VAR CalculationDate = 'Retail+Fleet'[Calculation date]
        
    /* CMIS Brand */
        VAR CMISBrand = 'Retail+Fleet'[CMIS Brand]
                
    /* Model */
        VAR CarModel = 'Retail+Fleet'[Model]

    /** Filter all contracts to see whether they fall within the specific conditions, and check whether the creation date falls within the to and from date range.
        The BLANK() value ensures that for the lines where the Car Model or the Duration is not filled in, the filter option is not stopped.
    **/

RETURN
    CALCULATE(FIRSTNONBLANK('Special actions'[Budget action],1),
        FILTER(
                'Special actions',
                'Special actions'[Product Class] = ProductPL
                && CalculationDate >= 'Special actions'[Date From]
                && CalculationDate <= 'Special actions'[Date To]
                && 'Special actions'[VN - VO] = NewCar
                && ('Special actions'[Duration] <= Duration || 'Special actions'[Duration] = BLANK())
                && 'Special actions'[CMIS Brand] = CMISBrand
                && ('Special actions'[Model] = CarModel || 'Special actions'[Model] = BLANK())
            ))

Lookup Table ('Special actions')

Product Class	CMIS Brand	Model	Duration	Date From	Date To	VN - VO	Budget action
PL	Brand 1		24	1/1/24	31/1/24	VN	800
PL	Brand 1		36	1/1/24	31/1/24	VN	1000
PL	Brand 1		48	1/1/24	31/1/24	VN	1000
PL	Brand 1	Model 1	48	1/1/24	31/1/24	VN	1500
PL	Brand 2	Model 2	48	1/1/24	31/1/24	VN	600
PL	Brand 3	Model 3	48	1/1/24	31/1/24	VN	600

Main Table ('Retail+Fleet')

Product Class	CMIS Brand	Model	Contract Duration	Calculation date	...	New/Used car	SpecialAction
AC	Brand 1	Model 6	36	20/12/23	...	VN
CC	Brand 1	Model 40	24	30/12/23	...	VN
OL	Brand 1	Model 1	60	3/1/24	...	VN
PL	Brand 2	Model 2	48	5/1/24	...	VN	600
PL	Brand 3	Model 4	48	12/1/24	...	VN
PL	Brand 1	Model 2	60	19/1/24	...	VO
PL	Brand 1	Model 1	24	30/1/24	...	VN	1500
PL	Brand 1	Model 3	36	2/2/24	...	VN
PL	Brand 2	Model 3	30	18/2/24	...	VO
PL	Brand 3	Model 2	72	1/3/24	...	VN

[lbendlin]

Cannot reproduce. Did you accidentally try to create a measure?

See attached.

[Anonymous · Accepted solution]

Thank you for reproducing my problem in any case! This helped enormously since I knew it was not a problem of calculation, rather a data-problem on my end. I found the problem in the end, where it was one of the columns not exactly matching the comparable column in the other table. So my code did not find any matches and therefore returned empty. Thank you!