Measure error, difference between rows

[Anonymous]

Dear community, 

I have this unexpected error happening on a measure mean to calculate the difference between two lines:

Trade up =

VAR Index = 'Market List'[Fare Index]

VAR Reference = 'Market List'[QF DATA.Cxr]

VAR HIGHER=

CALCULATE(

FIRSTNONBLANK('Market List'[QF DATA.AIF], TRUE()),

FILTER ('market list', 'Market List'[Fare Index] =Index -1 && 'Market List'[QF DATA.Cxr]=Reference)

)

return

if(

isblank (higher),

blank (),

min('Market List'[QF DATA.AIF])-Higher

)

the issue is with those bold Variables where I get:

A single value for column 'Fare Index' in table 'Market List' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

This measure comes from a previous file where this issue was not happening. 

Any idea how to fix it?

[Ashish_Mathur]

Hi,

Does this work

VAR Index = MIN('Market List'[Fare Index])

VAR Reference = MIN('Market List'[QF DATA.Cxr])

[Anonymous]

Hi @Ashish_Mathur Ashish, 

While Fare Index is a whole number value QF DATA.CXR is text, not sure I can use MIN here.

any alternative?

Thanks

Alberto

[Ashish_Mathur]

Hi,

Explain the business question, share some data and also show the expected result.

[Anonymous]

HI @Ashish_Mathur 

i'm trying to redo this:

https://community.powerbi.com/t5/Desktop/Difference-between-two-rows/m-p/557974#M262996

but fields have different names..

The goal is making the difference between 460 - 392.01 and the following rules.

hope it clarifies

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement based on your previous post:

Trade Up 2 =
VAR index =
    MAX ( 'QF Core Pricing'[Fare Index] )
VAR re =
    MAX ( 'QF Core Pricing'[Restriction] )
RETURN
    IF (
        re = "NP",
        CALCULATE ( MIN ( 'QF Core Pricing'[AIF] ) )
            - CALCULATE (
                MIN ( 'QF Core Pricing'[AIF] ),
                FILTER (
                    ALLSELECTED ( 'QF Core Pricing' ),
                    'QF Core Pricing'[Fare Index] = index - 1
                        && 'QF Core Pricing'[Restriction] = "P"
                )
            ),
        CALCULATE ( MIN ( 'QF Core Pricing'[AIF] ) )
            - CALCULATE (
                MIN ( 'QF Core Pricing'[AIF] ),
                FILTER (
                    ALLSELECTED ( 'QF Core Pricing' ),
                    'QF Core Pricing'[Fare Index] = index
                        && 'QF Core Pricing'[Restriction] = "NP"
                )
            )
    )

If it doesn't meet your requirement, Could you please provide a sample mockup table based on fake data or describle the fields of each tables and the relations between tables simply? Please don't have any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

HI @v-lid-msft 

i tried to readapt according to my queries:

Trade UP QF = 
VAR Index=
    MAX ('Market List (Tariff)'[Fare Index])
VAR Re=
    max('QF DATA'[Peak])
RETURN
IF(
    RE="Non Peak",
    calculate (MIN ('Market List (Tariff)'[QF DATA.AIF])    )
    - CALCULATE(
        min ('Market List (Tariff)'[QF DATA.AIF]),
        filter (
            ALLSELECTED('Market List (Tariff)'),
            'Market List (Tariff)'[Fare Index] = Index - 1
                && 'Market List (Tariff)'[QF DATA.Peak]="Peak"
        )
    ),
    calculate(min('Market List (Tariff)'[QF DATA.AIF])  )
    - calculate(
        min ('Market List (Tariff)'[QF DATA.AIF]),
        filter (
            ALLSELECTED('Market List (Tariff)'),
            'Market List (Tariff)'[Fare Index]=Index
                && 'Market List (Tariff)'[QF DATA.Peak]="Non Peak"
        )   
    )
)

but it doesn't come up as expected as some fields have changed:

So, let's take as example this market: the fields we have to consider to calculate the difference between lines is the Fare Basis column and the Peak column. In this screenshot we only see "peak fares" but they may be Peak or Non Peak.

Fare basis ending in 14 belong to a group of fares and the ones without to another: these 2 groups shouldn't combine together hence the result we would like to see is:

SDQZ14 326.00 - NDQZ14 260 = 66

SDQZ 329.99 - NDQZ 263.99 = 66

and so on for the others Peak, and non peak.

But as you can see from this other example some trade up worked...

let me know if clear enough