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

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