Translate the dax code to evaluate in Dax studio

[han_rj]

Please may I have help know How should the below calulated column be coded in dax studio

productrankdense =

VAR currentrow = 'Product'[Unit Price]

RETURN COUNTROWS(FILTER(VALUES('Product'[Unit Price]),'Product'[Unit Price] > currentrow)) +1

[Syndicate_Admin]

If you want to add a calculated column that includes the data that passes the filter for each row of the original table, please try the following code in dax studio or in the dax query view.
Please if I answered your query accept the solution, kudos 😀 are accepted

DEFINE
    COLUMN 'Product'[NewCalculatedColum] =
        VAR PriceOfCurrentProduct = 'Product'[Unit Price]
        VAR MoreExpensiveProducts =
            FILTER (
                'Product',
                'Product'[Unit Price] > PriceOfCurrentProduct
            )
        RETURN
            CONCATENATEX (
                MoreExpensiveProducts,
                'Product'[Unit Price],
                UNICHAR ( 10 )
            )

EVALUATE
Product

[Syndicate_Admin · Accepted solution]

If you need to add a calculated column showing the prices that pass the filter for each row, please use the following code in dax studio or in the powerbi query view. Please note that nested tables are not accepted in a cell of a powerbi table, therefore the value presented is a concatenation of the values in the field (you may not be able to see all the values included in the concatanation due to string constraints in that cell)
If I responded to your query, please bookmark the post as a solution. Kudos 😀 are accepted

DEFINE COLUMN 'Product'[NewCalculatedColum] =
        VAR PriceOfCurrentProduct = 'Product'[Unit Price]
        VAR MoreExpensiveProducts =
            FILTER (
                'Product',
                'Product'[Unit Price] > PriceOfCurrentProduct
            )
        RETURN
            CONCATENATEX (
                MoreExpensiveProducts,
                'Product'[Unit Price],
                UNICHAR ( 10 ), 'Product'[Unit Price], DESC
				
            )

EVALUATE
Product ORDER BY Product[Unit Price] DESC

[Anonymous]

Hi @han_rj ,
If you want to view the table generated by the filtering logic, you can use the ADDCOLUMNS function to create a temporary table containing the results of the filtering.

FilteredTable =
VAR currentrow = 'Product'[Unit Price]
RETURN
    ADDCOLUMNS(
        FILTER(
            ALL('Product'),
            'Product'[Unit Price] > currentrow
        ),
        "Filtered Unit Price", 'Product'[Unit Price]
    )

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[han_rj]

Hi, Thank You for helping but I hit this error when I tried the attached code

[Anonymous]

Hi @han_rj ,

Create a calculate table instead of a measure.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[han_rj]

Yes I tried it on a calculated table and that is when I hit the error

[Anonymous]

Hi @han_rj ,

Based on your description, it seems like you want to write code in the DAX query view to see the logic that the filter code block is running. The operation in context is a continuous process, and we can only show what the final generated filter table looks like in a certain context by giving a fixed value. You can try the following code and change the value of currentrow to the value you want to know at a certain point in time

EVALUATE
VAR currentrow = 50
RETURN
FILTER(
    ADDCOLUMNS(
        VALUES('Product'[UnitPrice]),
        "CurrentRow", currentrow,
        "IsGreater", IF('Product'[UnitPrice] > currentrow, 1, 0)
    ),
    [IsGreater] = 1
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[han_rj]

Please correct my understanding if we give a fixed value to 

VAR currentrow = 50

we can expand the Filter code block and view the table created

but we cannot view what the resultant table is by the concept of dynamically selecting row context like this

VAR currentrow = 'Product'[Unit Price]

[Syndicate_Admin]

For the calculated column you need to somehow aggregate the data to get a scalar. Failing that, you would be generating a table, which is not an accepted data type for a cell in a calculated column.
By using a fixed value for currentrow and running the EVALUATE with a table formula, you can get the result you expect as a table, but it doesn't work as scaling for a calculated column.

There is no way to generate the row context in a "fixed" way to generate a single table, it would generate a table for each row, but it is not clear how you should add each generated table to be part of each cell in a calculated column. Please review a previous code I shared using concatenatex to add the different values that would be in the filter for each row.
I hope this explanation serves to resolve the doubt.

[Anonymous]

Hi @han_rj ,

Your understanding is correct.

Best regards,
Albert He

[anmolmalviya05]

Hi , Please try below solution:

EVALUATE
ADDCOLUMNS(
'Product',
"ProductRankDense",
VAR currentrow = 'Product'[Unit Price]
RETURN
COUNTROWS(
FILTER(
VALUES('Product'[Unit Price]),
'Product'[Unit Price] > currentrow
)
) + 1
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner

@han_rj

[han_rj]

Thanks for helping, The main part I wanted to translate was the Filter table generated

As highlighted I want to view the table that is been generaed as a result of Filter logic

[anmolmalviya05]

Please try this:

EVALUATE
ADDCOLUMNS(
VALUES('Product'[Unit Price]),
"FilteredTable",
CALCULATETABLE(
VALUES('Product'[Unit Price]),
'Product'[Unit Price] > 'Product'[Unit Price]
)
)

[han_rj]

Hi, Thank You for trying I did check this solution, the resultant column is empty.

The reason for my query on how to convert the dax expression to dax studio code,  is to learn how to turn the row context dax line to dax studio executable code and view how the filter dax table will look