DAX - CALCULATE Actualized vs. Planned

Measure:
 .Inflation_PLAN = CALCULATE(DIM_Prem_Plan[.Vlr_Inflation_PLAN], FILTER(DIM_Prem_Plan, DIM_Prem_Plan[PK_Plan]IN VALUES(FAT_DB[PK_Plan])))

Power Query - Connect API REST API Bearer

 

FNC_Access_Token

() => 

let

    url = "https://XPTO_SITE_TOKEN",

    body  = "{ ""user"": ""XPTO_USER"", ""pwd"": ""XPTO_PASS""} ",

    AccessToken = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"], Content=Text.ToBinary(body)])),

    access_token = AccessToken[token]

in

    access_token

FNC_Table_Prep

let

  // Get_Data

  Query = () => let

    Source = Json.Document(Web.Contents("XPTO_SITE_SEARCH", [Headers=[Authorization="Bearer " & Fnc_Access_Token()]])),

    #"Converted to Table" = Record.ToTable(Source),

    Value = #"Converted to Table"{4}[Value]

in

    Value

in

  Query

DIM_Table

let

    Source = Fnc_Table_Prep(),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "user", "phone", "profile", "hLastUpdate", "active", "activationCode", "userName", "birthday", "gender", "followingCategories", "isProfileComplete", "name", "dob"}, {"_id", "user", "phone", "profile", "hLastUpdate", "active", "activationCode", "userName", "birthday", "gender", "followingCategories", "isProfileComplete", "name", "dob"})

in

    #"Expanded Column1"

Power Query - Block IF THEN ELSE

 let

    Source = (ano as text) => let 

    

        EmptyTable = #table({"/BIC/ZMATERIAL",...}, {}),

        Source = PowerBI.Dataflows(null),

        //THEN

        WorkspacePRO = Source{[workspaceId="564564563466353653665"]}[Data],

        DataflowPRO = WorkspacePRO{[dataflowName=Text.Combine({"MIX_Full_", ano})]}[Data],

        FAT_PricesPRO = DataflowPRO{[entity=Text.Combine({"MIX_FullDetail_", ano})]}[Data],

    

        //ELSE

        WorkspacePPU = Source{[workspaceId="356633456346456456456"]}[Data],

        DataflowPPU = WorkspacePPU{[dataflowName=Text.Combine({"MIX_Full_", ano})]}[Data],

        FAT_PricesPPU = DataflowPPU{[entity=Text.Combine({"MIX_FullDetail_", ano})]}[Data],

        Final_Table = if PRO_PPU = "PRO" then 

        FAT_PricesPRO else 

        FAT_PricesPPU,

        

        Final_Table2 = try Final_Table otherwise EmptyTable

    in 

        Final_Table2

in

    Source

Power Query - Connect Onedrive

 let

Source = SharePoint.Files("https://XXX.sharepoint.com/personal/XXX_com/", [ApiVersion = 15]),

#"Linhas Filtradas" = Table.SelectRows(Source, each Text.Contains([Folder Path], "XPTO_FOLDER")),
Step2 = Table.SelectColumns(#"Linhas Filtradas", {"Content"}),
Step3 = Table.AddColumn(Step2, "Cols", each Excel.Workbook([Content])),
Step4 = Table.RemoveColumns(Step3, {"Content"}),
Step5 = Table.ExpandTableColumn(Step4, "Cols", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
#"Linhas Filtradas1" = Table.SelectRows(Step5, each ([Kind] = "Sheet")),
#"Linhas Filtradas2" = Table.SelectRows(#"Linhas Filtradas1", each [Name] = "Dados"),
Step7 = Table.RemoveColumns(#"Linhas Filtradas2", {"Item", "Kind", "Hidden", "Name"}),
#"Data Expandida" = Table.ExpandTableColumn(Step7, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"})
in
#"Data Expandida"

Power Query - IF THEN ELSE

 let

    Source = PowerBI.Dataflows(null),

    #"Added Custom" = Table.AddColumn(#"Expanded DIM_Prem_Cambio", "Value_USD", each if [Currency] = "USD" then  [Value]   

else if [Currency] = "EUR" then ([Value] * [EUR])   

else if [Currency] = "BRL" then ([Value] * [BRL]) 

else if [Currency] = "CAD" then ([Value] * [CAD]) 

else if [Currency] = "CHF" then ([Value] * [CHF]) 

else if [Currency] = "GBP" then ([Value] * [GBP]) 

else if [Currency] = "KRW" then ([Value] * [KRW]) 

else if [Currency] = "MYR" then ([Value] * [MYR]) 

else if [Currency] = "RMB" then ([Value] * [RMB]) 

else if [Currency] = "VND" then ([Value] * [VND]) 

else if [Currency] = "INR" then ([Value] * [INR]) 

else if [Currency] = "JPY" then ([Value] * [JPY]) 

else if [Currency] = "CNY" then ([Value] * [CNY]) 

else if [Currency] = "SEK" then ([Value] * [SEK]) 

else 0),

in

    #"Added Custom"

DATAFLOW PPU x PRO

Workspace PPU - (USER PPU)

Resource - Create table_PPU (Enable load - OFF) 

Workspace PRO - (USER PPU)

Resource - Create table_PRO_Prep (load table_PPU) (Enable load - OFF)

Load table_PRO (table_PRO_Prep) (Enable load - ON)

Workspace PRO - (USER PRO)

Create PBIX(load table_PRO) (Enable load - ON)

Power Query - Replace Function letter fnc_replace

(TextValue as nullable text) =>

let

    // If TextValue is null, replace it with an empty string

    SafeText = if TextValue = null then "" else TextValue,

    // List of replacement pairs (accents and digits)

    AccentsList =

    {

        {null, "."},

        {"à","a"},{"á","a"},{"â","a"},{"ã","a"},{"ä","a"},

        {"è","e"},{"é","e"},{"ê","e"},{"ë","e"},

        {"ì","i"},{"í","i"},{"î","i"},{"ï","i"},

        {"ò","o"},{"ó","o"},{"ô","o"},{"õ","o"},{"ö","o"},

        {"ù","u"},{"ú","u"},{"û","u"},{"ü","u"},

        {"À","A"},{"Á","A"},{"Â","A"},{"Ã","A"},{"Ä","A"},

        {"È","E"},{"É","E"},{"Ê","E"},{"Ë","E"},

        {"Ì","I"},{"Í","I"},{"Î","I"},

        {"Ò","O"},{"Ó","O"},{"Ô","O"},{"Õ","O"},{"Ö","O"},

        {"Ù","U"},{"Ú","U"},{"Û","U"},{"Ü","U"},

        {"ç","c"},{"Ç","C"},{"ñ","n"},{"Ñ","N"},

        {"0",""},{"1",""},{"2",""},{"3",""},{"4",""},

        {"5",""},{"6",""},{"7",""},{"8",""},{"9",""}

    },

    // Convert the text into a list of characters, removing any null entries

    TextCharacters = List.RemoveNulls(Text.ToList(SafeText)),

    // Replace characters according to AccentsList and then combine back into a single string

    CleanedText = Text.Combine(List.ReplaceMatchingItems(TextCharacters, AccentsList), "")

in

    CleanedText