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

Power BI - Motor de Alarmisticas via Visual Table (Python Azure DAX PBIX)

 Step by step para buscar dados de dentro de um Visual Table 

1. Criar PBIX e gerar a tabela com as informações minimas ( 

   E-mail 
1. Valor a Monitorar 
2. Valor Range In 
3. Valor Range out) 
2. Gerar query via PBIX 
3. Executar/Testar query via DaxStudio 
4. Ajustar query JSON para execução REST API  
1. Replace:  " por \"  
5. Testar query via REST API 

 

 

 

 

 

ID	Descrição
1
2
3
4	{    "queries": [      {        "query":   "  DEFINE    VAR __DS0Core =       SUMMARIZECOLUMNS(        ROLLUPADDISSUBTOTAL(          ROLLUPGROUP('DIM_Alarmes'[Email], 'DIM_Alarmes'[Status]), \"IsGrandTotalRowTotal\"        ),        \"v_Valores\", 'DIM_Alarmes'[.Valores],        \"v_qtdRegs\", 'DIM_Alarmes'[.qtdRegs]      )       VAR __DS0PrimaryWindowed =       TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'DIM_Alarmes'[Status], 0, 'DIM_Alarmes'[Email], 1)     EVALUATE    __DS0PrimaryWindowed     ORDER BY    [IsGrandTotalRowTotal] DESC, 'DIM_Alarmes'[Status] DESC, 'DIM_Alarmes'[Email]     "      }    ],    "serializerSettings": {      "includeNulls": true    }     }
5	https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group#code-try-0
6