How to adjust DAX to use a measure instead of a calculated column

[JRHans09]

I would like to use the following DAX in a measure and place the measure in a table (currently it is a calculated column and works correctly):

USDSell3 = 
VAR BaseSellExcl =
CALCULATE(
    SUMX(
        Services,
        Services[BaseSell] - Services[BaseSellTax]
    ),
    Services[InclRev] = "Y"
)

VAR Result =
CALCULATE(
    BaseSellExcl * [Fx to USD]
)

RETURN
Result

The Fx to USD (measure) reference in the calculated column above is here:

Fx to USD = 
IF(
    SELECTEDVALUE( Job[Base Currency] ) = "USD",
    1,
    MAXX(
        TOPN(
            1,
            FILTER(
                ALL( ForexRates ),
                ForexRates[Base Currency] = SELECTEDVALUE( Job[Base Currency] )
                    && ForexRates[Forex Date] <= MAX( Job[DateService] )
                ),
            ForexRates[Forex Date], DESC
            ),
        ForexRates[Debtor Rate]
        )
)

The Services Table and the Jobs Table are linked by a unique job reference number (key ID).

Currently, I have written DAX in both a measure and a calculated column, but only when I place the calculated column into a table visual (with other data from the Jobs table) do the values appear correctly. If I try to place the measure into the table visual (with data from the Jobs table), I get multiple records with unrelated data (presumably due to filter context), and only after extended load time.

Here is sample screen shot of the Jobs table visual:

USDSell3 (calculated column) in the above appears correctly.

Here is a sample screen shot of the Services table visual:

USDSell (measure) in the above appears correctly. It is the same DAX code from USDSell3(calculated column), but in a measure,

Is it possible to adjust the DAX to be used in a measure so that I can place that measure into the Jobs Table, with correct data appearing, instead of calculated columns? I have read that in most cases it is best to create measures vs calculated columns, but I cannot figure out the filter context in the DAX to use a measure. I would eventually create four more measures for Margin, Margin%, etc. and would prefer to use measures, if possible.

Thanks in advance for the help. Please let me know if additional information is needed.

[JRHans09 · Accepted solution]

I solved the problem by creating calculated columns in each of the related tables with the following DAX:

USDSell3 = 
VAR BaseSellExcl =
CALCULATE(
    SUMX(
        Services,
        Services[BaseSell] - Services[BaseSellTax]
    ),
    Services[InclRev] = "Y"
)

VAR Result =
CALCULATE(
    BaseSellExcl * [Fx to USD]
)

RETURN
Result

After those calculated columns were created, which maintains the filter context, I created multiple measures related to these columns - for margin, margin percentage, etc.. The added value of this solution is that with measures, Conditional Formatting for the measures is possible within the tables (font, background, etc.), without having to create additional measures to 'flag' the values for use in conditional formatting.

[V-lianl-msft]

Hi @JRHans09 ,

It is very difficult to analyze without looking at the data and just by imagining.

You can provide a sample pbix and include the formula you created in it, so that we can understand the context in which your formula runs, and see if we can help you rewrite the calculated column as measure.

Please remove any sensitive data before uploading.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.