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
ccowin
Helper I
Helper I

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

@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
jdbuchanan71
Super User
Super User

@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.

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.

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

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

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

comma.jpg

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

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.

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.

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

With Semicolons it doesnt work; I switched them all to commas and it DOES work:

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

Can anyone explain why this has happened? I really still dont understand why the commas are working when the semicolons should be the correct sintax. And then when I place that into my orignal code there are still errors.

Hey,

 

the separator of arguments "," or ";" is derived on the settings from the OS.

 

If you copy and paste code snippets from a text to your pbix, the argument separator will not automatically converted. This conversion just happens on opening the pbix.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Even when I type this **bleep**ing code out letter for letter it still refuses to actually associate what I'm typing with an actual table or column. I just tried restarting if as you say it resets on the start of the pbix then it should work and it doesnt. I really just feel like a complete idiot here. How does this not work?

If the variables work as individual measures does it work if you split it into 4 measures?

CurrentPrice =
CALCULATE (
    SUM ( Query1[ExchangeRate] ),
    FILTER (
        ALLEXCEPT ( Query1, Query1[Currencies] ),
        Query1[ExchangeRate] > 0
            && Query1[Date] = MAX ( Query1[Date] )
    )
)
PreviousDateWithPrice =
CALCULATE (
    MAX ( Query1[Date] ),
    FILTER (
        ALLEXCEPT ( Query1, Query1[Currencies] ),
        Query1[ExchangeRate] > 0
            && Query1[Date] < MAX ( Query1[Date] )
    )
)
PreviousPrice =
CALCULATE (
    SUM ( Query1[ExchangeRate] ),
    FILTER (
        ALLEXCEPT ( Query1, Query1[Currencies] ),
        Query1[Date] = [PreviousDateWithPrice]
    )
)
FinalMeasure = DIVIDE ( [CurrentPrice] - [PreviousPrice], [PreviousPrice], 0 )

If not, like @TomMartens suggested, could you share your .pbix?

Hey Guys,

 

Thanks for the interest over the weekend, I'll try and break it out into 3 seperate measures and see if that works and if not I'll upload the pbix for you.

While splitting the measure into 4 seperate measures it wasn't rejected for syntax but is also only giving 0 so there must be something else wrong.

 

Basically I have a list of weather stations that are all reporting their minimum daily battery voltage and I get that through an API that provides me with the last month of data points (There is also a connection to an excel file in sharepoint that our forecasters use to make the daily forecast pdf so it pulls that for future data).

 

I'd like to take the most recent and second most recent battery voltage and determine the rate of change and then graph this. This will be able to tell me which ones are dieing the quickest and I can then go to the settings and turn their reporting down as we go into winter and their solar panels begin to either get snow or just shorter days leads to less sunlight to charge.

 

Here is the link: https://www.dropbox.com/s/jwoq0kg908db1d1/PacifiCorp.pbix?dl=0

Thanks again @TomMartens  and @jdbuchanan71 

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

 

 

Thats it!

 

I must have copy/pasted the measure from someone's snippet into there and it must have been messing everything up.

 

Thanks so much for your guys help; I wouldn't have been able to get by myself!

Hey,

 

maybe you want to consider to share your pbix file, upload the file to onedrive or dropbox and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.