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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
felipefrutuoso
Frequent Visitor

rankx using column of variable table

Why I cannot do this?

EVALUATE
VAR myvirtuatable = ADDCOLUMNS(
    SUMMARIZE(
        FILTER('table_a',[field]=666),        
        'table_a'[field2],
        'table_a'[field3]
             ),
    "total", CALCULATE(sum('table_a'[value]))
        )

RETURN ADDCOLUMNS(myvirtuatable,"RANK",RANKX(myvirtuatable,total,,DESC))

"cannot find this name, "total"

1 ACCEPTED SOLUTION
edhans
Super User
Super User

You can, your syntax is off just a bit though @felipefrutuoso 

When you create a table using a variable, if the function can see the table (iterators can, aggregators cannot), the field name has to be wrapped in square brackets.

 

EVALUATE
VAR myvirtuatable =
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(
                'table_a',
                [field] = 666
            ),
            'table_a'[field2],
            'table_a'[field3]
        ),
        "total",
            CALCULATE(
                SUM( 'table_a'[value] )
            )
    )
VAR Result = 

    ADDCOLUMNS(
        myvirtuatable,
        "RANK",
            RANKX(
                myvirtuatable,
                [total],   <---- Adjustment to syntax
                ,
                DESC
            )
    )
RETURN
	Result

See if that works for you.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
edhans
Super User
Super User

You can, your syntax is off just a bit though @felipefrutuoso 

When you create a table using a variable, if the function can see the table (iterators can, aggregators cannot), the field name has to be wrapped in square brackets.

 

EVALUATE
VAR myvirtuatable =
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(
                'table_a',
                [field] = 666
            ),
            'table_a'[field2],
            'table_a'[field3]
        ),
        "total",
            CALCULATE(
                SUM( 'table_a'[value] )
            )
    )
VAR Result = 

    ADDCOLUMNS(
        myvirtuatable,
        "RANK",
            RANKX(
                myvirtuatable,
                [total],   <---- Adjustment to syntax
                ,
                DESC
            )
    )
RETURN
	Result

See if that works for you.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors