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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
aliahad
Frequent Visitor

Trying to get Max value using a column parameter

I have a table where I have added a column with an IF statment

RowNumberYear = IF(VALUE(MAX('gt EMPLOYEE'[YEAR])) = VALUE('Head Count'[YEAR]), 1, 0)  

I am trying to get Max value using following expression:

RowNumber = 
var FilteredTable = 
FILTER('Head Count', 'Head Count'[RowNumberYear] = 1)
RETURN
MAX(FilteredTable[MONTH])

I am getting the following error message:

Table variable 'FilteredTable' cannot be used in current context because a base table is expected.

Screenshot 2025-06-05 171126.png

Any help is appreciated.

 

Thanks,

Ali.

1 ACCEPTED SOLUTION

Hi @aliahad ,

I think @Irwan s approach was correct and solves the ask. However, to be specific, considering there is only month and year column, the solution will be as below.

Rupak_bi_0-1749559729272.pngRupak_bi_1-1749559743036.png

If this solves, Plz accept as solution



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/

View solution in original post

8 REPLIES 8
aliahad
Frequent Visitor

Hi @Irwan ,

 

I tried the expression you provided, it gives me the 'MAX' for all the rows in the dataset.

 

Screenshot 2025-06-05 190858.png

So in my dataset I have a column 'MONTH' and 'YEAR' and I need to get a calculated column that gives me 1 for 'MAX' month and 'MAX' year, and 0 for all the remainder rows. The current dataset got max year as 2025 and max month as 5, and in my column I need 1 for MAX year 2025 and MAX month 5 and 0 for remainder of the rows. This should switch when the June data is added to the dataset.

 

I hope I have clarify the ask.

 

Thanks,

Ali.

Hi @aliahad ,

I think @Irwan s approach was correct and solves the ask. However, to be specific, considering there is only month and year column, the solution will be as below.

Rupak_bi_0-1749559729272.pngRupak_bi_1-1749559743036.png

If this solves, Plz accept as solution



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/
maruthisp
Super User
Super User

Hi aliahad,

The error you're encountering in Power BI DAX, is due to the fact that you're trying to use a table variable (FilteredTable) directly inside a scalar function like MAX(), which expects a column reference from a base table, not a variable.


Please try below DAX expression:
RowNumber =
CALCULATE(
MAX('Head Count'[MONTH]),
'Head Count'[RowNumberYear] = 1
)

Hope the above DAX expression may help you.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

Irwan
Super User
Super User

hello @aliahad 

 

as far as my knowledge, MAX() only accept column that has been defined.

Irwan_3-1749164082197.png

DAX doesnt accept force command as my example above.

 

instead, you can do as below.

Irwan_4-1749164140008.png

 

Hope this will help.

Thank you.

 

aliahad
Frequent Visitor

Hi @Irwan ,

 

I tried the expression you provided, it gives me the 'MAX' for all the rows in the dataset.

 

Screenshot 2025-06-05 190858.png

So in my dataset I have a column 'MONTH' and 'YEAR' and I need to get a calculated column that gives me 1 for 'MAX' month and 'MAX' year, and 0 for all the remainder rows. The current dataset got max year as 2025 and max month as 5, and in my column I need 1 for MAX year 2025 and MAX month 5 and 0 for remainder of the rows. This should switch when the June data is added to the dataset.

 

I hope I have clarify the ask.

 

Thanks,

Ali.

hello @aliahad 

 

i assumed you want to get MAX value of your date (max month AND max year).

Here is a simple example.

Irwan_0-1749189331879.png

Check =
var _Max = MAX('Table'[Date])
Return
IF(
    'Table'[Date]=_Max,
    1,
    0
)

 

but if you need separate column between month and year, then please try this.

Irwan_1-1749189531805.png


Check Month =

var _Max = MAX('Table'[Month])
Return
IF(
    MONTH('Table'[Date])=_Max,
    1,
    0
)
Check Year =
var _Max = MAX('Table'[Year])
Return
IF(
    YEAR('Table'[Date])=_Max,
    1,
    0
)

 

Hope this will help.

Thank you.

aliahad
Frequent Visitor

Hi @Irwan 

 

I don't have a date field in my dataset, the MONTH and YEAR are the only fields available for me and I need to get the MAX of the year then the MAX of the month from the dataset. 

 

Thanks,

Ali.

aliahad
Frequent Visitor

Hi @Irwan 

The DAX expression you provided is giving me max value for all the rows.

Screenshot 2025-06-05 190858.png

Column = CALCULATE(
                    MAX('Head Count'[MONTH]),
                    FILTER('Head Count', 'Head Count'[RowNumberYear] = 1)
)   

So in my dataset I have YEAR and MONTH, and I trying to get 1 or 0 in the calculated column for max year and month, so the current max month I got in the dataset is  5 and current max year is 2025, but when I new data for June I want current max month = 6 and current max year = 2025 and next month the max month will be July. So I am trying to create a flag using the calculated column.

 

Thanks,

Ali.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.