DAX - Sample RLS

After create rules:

https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

dataset_RLS:

[Email] =  userprincipalname() 

or

[Email] =  USERNAME() 

Por exemplo, se o nome de usuário completo de um usuário for "jane.doe@contoso.com", a função USERNAME() retornaria apenas "jane.doe", enquanto a função USERPRINCIPALNAME() retornaria "jane.doe@contoso.com".

Em resumo, a função USERNAME() é útil quando você precisa apenas do nome de usuário sem o domínio, enquanto a função USERPRINCIPALNAME() é útil quando você precisa do identificador exclusivo do usuário.

DIM_Reunioes_Plan:

[SME] = LOOKUPVALUE(dataset_RLS[SME],dataset_RLS[Email],userprincipalname())

Power Query - Read DB Access and accdb files

 let

    Source = 

    //Access.Database(File.Contents("C:\Users\biprio\PRIO ENERGY SA(1)\W Analytics - BigData\datasets\SPANCOP\APOfiles\prio-truck prg.accdb"), [CreateNavigationProperties=true])

    OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\temp\FILE.accdb"";Jet OLEDB:Database Password="""";", [Query="SELECT * FROM TABLE "]),

    #"Removed Columns" = Table.RemoveColumns(Source,{"CDESCR"}),

    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [NIF] <> null and [NIF] <> "")

in

    #"Filtered Rows"

PBIX - DAX - Combine Hibrid Years Recursive

 

Configuração via Power Query:

-----------------------------------------------------------

1 - Criar Parameter Anos

"2022,2023" meta [IsParameterQuery=true, List={"Diario", "2018", "2019", "2020", "2018,2019,2020", "2021"}, DefaultValue="Diario", Type="Text", IsParameterQueryRequired=true]

-----------------------------------------------------------

2 - Criar Função que fará chamada recursiva

let

    Source = (ano) => let 

    

        EmptyTable = #table({"CALDAY", "DOC_CURRCY", "SALES_UNIT", "CUST_SALES", "ZMATERIAL", "DISTR_CHAN", "DIVISION", "SALESORG", "PLANT", "DOC_TYPE", "ZWS_PAYMD", "DOC_CATEG", "HH", "ZWS_SUBTP", "ZWS_SBPAY", ".NIF_Venda", "ZOPERADOR", "ZCARDNUM1", "GROSS_VAL", "TAX_VALUE", "CML_OR_QTY", "NET_VALUE"}, {}),

        Source = PowerBI.Dataflows(null),

        Workspace = Source{[workspaceId="786887ff-e45d-4d89-b874-b99d907cd9c3"]}[Data],

        Dataflow = Workspace{[dataflowName=Text.Combine({"Prio_BW_DF_Vendas_", ano})]}[Data],

        #"Removed Other Columns" = Table.SelectColumns(Dataflow,{"Data"}),

        #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", 

{"CALDAY", "DOC_CURRCY", "SALES_UNIT", "CUST_SALES", "ZMATERIAL", "DISTR_CHAN", "DIVISION", "SALESORG", "PLANT", "DOC_TYPE", "ZWS_PAYMD", "DOC_CATEG", "HH", "ZWS_SUBTP", "ZWS_SBPAY", ".NIF_Venda", "ZOPERADOR", "ZCARDNUM1", "GROSS_VAL", "TAX_VALUE", "CML_OR_QTY", "NET_VALUE"}, 

{"CALDAY", "DOC_CURRCY", "SALES_UNIT", "CUST_SALES", "ZMATERIAL", "DISTR_CHAN", "DIVISION", "SALESORG", "PLANT", "DOC_TYPE", "ZWS_PAYMD", "DOC_CATEG", "HH", "ZWS_SUBTP", "ZWS_SBPAY", ".NIF_Venda", "ZOPERADOR", "ZCARDNUM1", "GROSS_VAL", "TAX_VALUE", "CML_OR_QTY", "NET_VALUE"}),

        Final_Table = try #"Expanded Data" otherwise EmptyTable

        

    in 

        Final_Table

in

    Source

-----------------------------------------------------------

3 - Criar TABLE:

let

    Parameters = Text.Split(Anos, ","),

    ParameterTable = Table.FromList(Parameters, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns" = Table.RenameColumns(ParameterTable,{{"Column1", "Ano"}}),

    TableList = Table.AddColumn(#"Renamed Columns", "Tables", each Get_FAT_Vendas([Ano])),

    CombinedTables = Table.Combine(TableList[Tables])

in

    CombinedTables

-----------------------------------------------------------

DAX - Comparação de informações entre 2 tabelas (PROCV)

.Vlr_PF_Diff =

CALCULATE ( SUMX ( Table01, Table01[Valor_Compara01] - CALCULATE ( SUMX ( RELATEDTABLE ( Table02 ), (Table02[Valor_Compara02] )), FILTER ( Table02 , AND(Table02[Var_A] = Table01[Var_A], AND(Table02[Var_B] = Table01[Var_B] , Table02[Var_C] = Table01[Var_C] )) ) ) ) )

Postgres SQL - AWS Connect

 To disable encryption

1. File --> Options and settings --> Data source settings
2. Click Edit Permissions on your connection
3. Uncheck Encrypt Connections

==================================================

Power Query:

let

    Source = Value.NativeQuery(PostgreSQL.Database("database.XPTO.com.br", "DBName", [CreateNavigationProperties=false]),
"select#(lf)  table_name,#(lf)  pg_size_pretty(pg_relation_size(quote_ident(table_name))),#(lf)  pg_relation_size(quote_ident(table_name))#(lf)from information_schema.tables#(lf)where table_schema = 'public'#(lf)order by 3 desc", null, [EnableFolding=true])

in

    Source

=============================

select

  table_name,

  pg_size_pretty(pg_relation_size(quote_ident(table_name))),

  pg_relation_size(quote_ident(table_name))

from information_schema.tables

where table_schema = 'public'

order by 3 desc;

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')

from information_schema.tables

order by 3

DAX - Running total based on ranking index - Acumulado Ranking

Rank = 

RANKX (

    SUMMARIZE (

        ALLSELECTED ( FAT_TABLE ),

        FAT_TABLE[YYYYMM],

        FAT_TABLE[VAR1]

    ),

    CALCULATE ( SUM ( FAT_TABLE[Price (€/t)] ) ),

    ,

    ASC,

    DENSE

)

=============================================== 

Running VAR_Sum = 

IF (

    ISINSCOPE ( FAT_TABLE[VAR1] ) && ISINSCOPE ( FAT_TABLE[YYYYMM] ),

    VAR CurrentRank = [Rank]

    VAR SummarizeFAT_TABLE =

        CALCULATETABLE (

            SUMMARIZE ( FAT_TABLE, FAT_TABLE[VAR1], FAT_TABLE[YYYYMM] ),

            ALLSELECTED ( FAT_TABLE )

        )

    VAR FAT_TABLESales =

        ADDCOLUMNS ( SummarizeFAT_TABLE, "@TotalSum", [VAR_Sum], "@Rank", [Rank] )

    VAR Result =

        SUMX ( FILTER ( FAT_TABLESales, [@Rank] <= CurrentRank ), [@TotalSum] )

    RETURN

        Result

)

=============================================== 

Power Query - Convert 18-digit LDAP/FILETIME timestamps

 Convert 18-digit LDAP/FILETIME timestamps

Power Query:

  #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Replaced Value9", ".DateLastLogon", each [lastLogon] / 864000000000), {{".DateLastLogon", type datetime}}),

  #"Added custom2" = Table.TransformColumnTypes(Table.AddColumn(#"Added custom", ".DateLastLogonTimestamp", each [lastLogonTimestamp] / 864000000000), {{".DateLastLogonTimestamp", type datetime}}),

  #"Added custom3" = Table.TransformColumnTypes(Table.AddColumn(#"Added custom2", ".DateAccountExpires", each [accountExpires] / 864000000000), {{".DateAccountExpires", type datetime}}),

  

Font help me:

https://www.epochconverter.com/ldap

864000000000

80589246768

20000000000

1676040067