How to format members Roles expressions?

[rvgfox]

hi,

I'm using in my scripts:

FormatDax(Model.AllMeasures, true);

But' obviously it doesn't works with the member's role expressions.

Is it possible to do that?

[DBojsen]

Hi @rvgfox

This should give you what you need:

List<string> messages = new List<string>();

// Run through all roles
foreach (var role in Model.Roles)
{
    // Run through all tables to see if an RLS expression is set
    foreach (var table in role.RowLevelSecurity.Keys)
    {
        if (!string.IsNullOrEmpty(role.RowLevelSecurity[table]))
        {
            // Format the DAX expression
            role.RowLevelSecurity[table] = FormatDax(role.RowLevelSecurity[table]);
            messages.Add($"Role {role.Name} - Table {table}");
        }
    }
}

if (messages.Count > 0)
{
    Info("Formatted role expressions for:\r\n" + string.Join("\r\n", messages));
}
else
{
    Info("Didn't find any role expressions to format!");
}

Best Regards

David - TE3 Support

[rvgfox]

@DBojsen I'm trying to run that script in TE2 but I get an error;

How can I solve it?

Thanks in advance

[rvgfox]

Thank you very much @DBojsen it works perfectly.

[DBojsen]

Hi @rvgfox

Here is a better script that works in both TE3 & TE2:

List<string> messages = new List<string>();

// Run through all roles
foreach (var role in Model.Roles)
{
    // Run through all table permissions
    foreach (var permission in role.TablePermissions)
    {
        FormatDax(permission);
        messages.Add("Role " + role.Name + " - Table " + permission.TableName);
    }
}

if (messages.Count > 0)
{
    Info("Formatted role expressions for:\r\n" + string.Join("\r\n", messages));
}
else
{
    Info("Didn't find any role expressions to format!");
}

[DBojsen]

If you don't care about the info box at the end, you can do it all in one line:

FormatDax(Model.Roles.SelectMany(r => r.TablePermissions));

[rvgfox]

Thank you very much @DBojsen

I've tested:

FormatDax(Model.Roles.SelectMany(r => r.TablePermissions));

And now, in TE2 I haven't the error message, thanks.

But I'm testing with this measure:

Calculate( 'Facturas Ventas'[Kilos Cafe Facturas Ventas] , Filter( 'Facturas Ventas' , 'Facturas Ventas'[Id_sk_Contratos] <> -1) )

With TE3 it's formated to:

CALCULATE( [Kilos Cafe Facturas Ventas], FILTER( 'Facturas Ventas', 'Facturas Ventas'[Id_sk_Contratos] <> -1 ) )

It's correct acording with BPA, but with TE2 it's formated to:

CALCULATE ( 'Facturas Ventas'[Kilos Cafe Facturas Ventas], FILTER ( 'Facturas Ventas', 'Facturas Ventas'[Id_sk_Contratos] <> -1 ) )

It doesn't follow the BP because "Measure references should not be qualified with the table name"

Some ideas?

[DBojsen]

Hi @rvgfox

TE3 uses our own formatting engine by default.
TE2 uses daxformatter.com which doesn't have the context of your model.

[rvgfox]

Thanks @DBojsen Will them use both, TE2 and TE3, the same engine in a near future?

[DBojsen]

Hi @rvgfox

Sorry - your comment slipped through my holiday email madness.
Both can use daxformatter.com, but there are no plans to bring our own formatting engine to TE2.

/ David

[mlonsk]

Hi @rvgfox
Just to follow up on David's remark, you can choose which formatter you want to use in Tabular Editor 3 Preferences:

Just in case you want the same output in TE2 and TE3.