cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ccowin Frequent Visitor
Frequent Visitor

Why isn't power bi referencing what I tell it to?

I'm trying to adapt the bellow measure I found on the forums to my needs but everytime I try and set a reference it describes it as an "Unexpected Expression". This seems to happen quite frequently and its unbelievably frustrating when I'm getting code that works for other people and there is no explanation why it doesn't work for me. I'm literally just replacing there column and table references and it just doesn't pick them up.

 

Measure =
VAR currentPrice =
    CALCULATE (
        SUM ( Query1[ExchangeRate] );
        FILTER (
            ALLEXCEPT ( Query1; Query1[Currencies] );
            Query1[ExchangeRate] > 0
                && Query1[Date] = MAX ( Query1[Date] )
        )
    )
VAR previousDateWithPrice =
    CALCULATE (
        MAX ( Query1[Date] );
        FILTER (
            ALLEXCEPT ( Query1; Query1[Currencies] );
            Query1[ExchangeRate] > 0
                && Query1[Date] < MAX ( Query1[Date] )
        )
    )
VAR previousPrice =
    CALCULATE (
        SUM ( Query1[ExchangeRate] );
        FILTER (
            ALLEXCEPT ( Query1; Query1[Currencies] );
            Query1[Date] = previousDateWithPrice
        )
    )
RETURN
    DIVIDE ( currentPrice - previousPrice; previousPrice; 0 )

Capture.png

As you can see it just gray's out when it is the same format as the original code that is said to work. What am I doing wrong?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Why isn't power bi referencing what I tell it to?

@ccowin 

When I opened your .pbix and looked at [5_BatVoltMinDay_ROC] I noticed a couple things.

There was an extra line with "Measure ="

One of the lines was compareing a name to be > 0

Once I swapped out the ; for , and fixed those the measure seems to work, wanted to check if that is what you are trying to get to.

BatteryMeasureCompare.jpg

Battery.jpg

 

The updated measure.

5_BatVoltMinDay_ROC = 

    
    VAR currentPrice =
        CALCULATE (
            SUM ( '3301 - 3331'[BatVoltMinDay] ),
            FILTER (
                ALLEXCEPT ( '3301 - 3331', '3301 - 3331'[StationID_Name] ),
                '3301 - 3331'[BatVoltMinDay] > 0
                    && '3301 - 3331'[day] = MAX ( '3301 - 3331'[day] )
            )
        )
    VAR previousDateWithPrice =
        CALCULATE (
            MAX ( '3301 - 3331'[day] ),
            FILTER (
                ALLEXCEPT ( '3301 - 3331', '3301 - 3331'[StationID_Name] ),
                '3301 - 3331'[BatVoltMinDay] > 0
                    && '3301 - 3331'[day] < MAX ( '3301 - 3331'[day] )
            )
        )
    VAR previousPrice =
        CALCULATE (
            SUM ( '3301 - 3331'[BatVoltMinDay] ),
            FILTER (
                ALLEXCEPT ( '3301 - 3331', '3301 - 3331'[StationID_Name] ),
                '3301 - 3331'[day] = previousDateWithPrice
            )
        )
    RETURN
        DIVIDE ( currentPrice - previousPrice, previousPrice, 0 )

 

 

View solution in original post

19 REPLIES 19
Super User
Super User

Re: Why isn't power bi referencing what I tell it to?

@ccowin 

Can you copy the code of your measure rather than a screen shot?  Hard to tell if there is a missing ; or ) behind the pop-up.

ccowin Frequent Visitor
Frequent Visitor

Re: Why isn't power bi referencing what I tell it to?

BatVoltMinDay_ROC = 
Measure =
VAR currentPrice =
    CALCULATE (
        SUM ( '3301 - 3331'[BatVoltMinDay] );
        FILTER (
            ALLEXCEPT ( '3301 - 3331','3301 - 3331'[StationID_Name] );
            '3301 - 3331'[BatVoltMinDay] > 0
                && '3301 - 3331'[day] = MAX ( '3301 - 3331'[day] )
        )
    )
VAR previousDateWithPrice =
    CALCULATE (
        MAX ( '3301 - 3331'[day] );
        FILTER (
            ALLEXCEPT ( '3301 - 3331','3301 - 3331'[StationID_Name] );
            '3301 - 3331'[StationID_Name] > 0
                && '3301 - 3331'[day] < MAX ( '3301 - 3331'[day] )
        )
    )
VAR previousPrice =
    CALCULATE (
        SUM ( '3301 - 3331'[BatVoltMinDay] );
        FILTER (
            ALLEXCEPT ( '3301 - 3331','3301 - 3331'[StationID_Name] );
            '3301 - 3331'[day] = previousDateWithPrice
        )
    )
RETURN
    DIVIDE ( currentPrice - previousPrice; previousPrice; 0 )

I dont understand why that would be different. I'm literally just copy/pasting my refrences over the original; not touching any syntax whatsoever.

 

The ones that are grayed out are line 8's BatVoltMinDay, line 9's day, line 17's StationID_Name, line 18's first day and line 26's day.

Super User
Super User

Re: Why isn't power bi referencing what I tell it to?

In your model, is [BatVoltMinDay] a measure?  In the original it is a column in a table.

ccowin Frequent Visitor
Frequent Visitor

Re: Why isn't power bi referencing what I tell it to?

BatVoltMinDay is a column set to decimal number, BatVoltMinDay_ROC is the only measure in the table.

Super User
Super User

Re: Why isn't power bi referencing what I tell it to?

Think I found it, you left in the , instead of ;

comma.jpg

ccowin Frequent Visitor
Frequent Visitor

Re: Why isn't power bi referencing what I tell it to?

I've tried both a , and a ; there and the comma lets the following Station ID_Name work. When switched to a semicolon it doesn't change anything

Super User
Super User

Re: Why isn't power bi referencing what I tell it to?

There were a couple spots with the , instead of ;.  Does this measure work?

BatVoltMinDay_ROC =
Measure
    =
    VAR currentPrice =
        CALCULATE (
            SUM ( '3301 - 3331'[BatVoltMinDay] );
            FILTER (
                ALLEXCEPT ( '3301 - 3331'; '3301 - 3331'[StationID_Name] );
                '3301 - 3331'[BatVoltMinDay] > 0
                    && '3301 - 3331'[day] = MAX ( '3301 - 3331'[day] )
            )
        )
    VAR previousDateWithPrice =
        CALCULATE (
            MAX ( '3301 - 3331'[day] );
            FILTER (
                ALLEXCEPT ( '3301 - 3331'; '3301 - 3331'[StationID_Name] );
                '3301 - 3331'[StationID_Name] > 0
                    && '3301 - 3331'[day] < MAX ( '3301 - 3331'[day] )
            )
        )
    VAR previousPrice =
        CALCULATE (
            SUM ( '3301 - 3331'[BatVoltMinDay] );
            FILTER (
                ALLEXCEPT ( '3301 - 3331'; '3301 - 3331'[StationID_Name] );
                '3301 - 3331'[day] = previousDateWithPrice
            )
        )
    RETURN
        DIVIDE ( currentPrice - previousPrice; previousPrice; 0 )

Just trying to eliminate stuff that might be causing the problem.

ccowin Frequent Visitor
Frequent Visitor

Re: Why isn't power bi referencing what I tell it to?

Unfortunately not, just breaks the StationID_Name following all the semicolons.

I'm gonna try taking the space out of the name of the table and see if that changes anything.

Super User
Super User

Re: Why isn't power bi referencing what I tell it to?

If you put the code from the first VAR in as it's own measure is it still broken?

 

Test Measure =
CALCULATE (
    SUM ( '3301 - 3331'[BatVoltMinDay] );
    FILTER (
        ALLEXCEPT ( '3301 - 3331'; '3301 - 3331'[StationID_Name] );
        '3301 - 3331'[BatVoltMinDay] > 0
            && '3301 - 3331'[day] = MAX ( '3301 - 3331'[day] )
    )
)

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,101)