Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure error, difference between rows

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?
 
 
6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Does this work

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

 

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"
                )
            )
    )

 

9.PNG

 

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.

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
Not applicable

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.
 

Capture.PNG

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

 

Capture 2.PNG

let me know if clear enough

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.