美文网首页
冲冲冲学DAX

冲冲冲学DAX

作者: 六六的建斌 | 来源:发表于2017-08-26 16:31 被阅读0次

    简单筛选选函数


    和下面的效果一样

    IF ( ISBLANK ( Sales[Quantity] ), 1, Sales[Quantity] )    用其检测是否为空

    IFERROR ( SQRT ( Test[Omega] ), BLANK() )     有错误用空值替代

    IF ( Sales[Quantity] = 0, BLANK(), Sales[Sales Amount] / Sales[Quantity] )   简单的条件判断语句

    DIVIDE ( Sales[Sales Amount], Sales[Quantity] )    安全除法,若被除数为0,则返回0

    Sales[Gross Margin] = Sales[Sales Amount] - Sales[Total Product Cost]  直接相减   计算列

    [Total Sales] := SUM ( Sales[Sales Amount] )   度量值

    SUMX ( Sales, Sales[Order Quantity] * Sales[Unit Price] )  参数( 表,表达式)。

    COUNTROWS ( Table ) = COUNTA ( Table[Column] ) + COUNTBLANK ( Table[Column] )  非空+   空等于总行数

    DISTINCTCOUNT ( Table[Column] ) = COUNTROWS ( DISTINCT ( Table[Column] ) )

    SUMX (

                                 FILTER ( Sales, Sales[Unit Price] > 10 ),

                                  Sales[Sales Amount]

    )                      计算上下文

    Sales[List Amount] = Sales[Order Quantity] * Products[List Price]      两表建立关系

    Sales[List Amount] = Sales[Order Quantity] * RELATED ( Products[List Price] )  没有建立关系则有这个函数

    Products[Number Orders] = COUNTROWS ( RELATEDTABLE ( Sales ) )

    Products[List Amount] =

    SUMX (

    RELATEDTABLE ( Sales ),

    Sales[Order Quantity] * Products[List Price]

    )   好像是两表之间是多对一的关系的话就用RELATEDTABLE

    Products[Price Ranking] =

    COUNTROWS (

    FILTER (

    Products,

    Products[List Price] > EARLIER ( Products[List Price] )

    )

    )    EARLIER表示一个变动的参数,表示在当前的自己之前的数,语句意思是计算价格排名,计算在产品表里面产品的价格比自己高的数的个数,计算行多少就是有多少高出自己。

    CALCULATE (表达式  条件,条件  ...)

    [Sales2006] :=

                  CALCULATE (

                                              SUM ( Sales[Sales Amount] ),

                                               Order Date[Year] = 2006

    )



    增加筛选条件,更颗粒化的选择数据

    [Sales2006] :=

    CALCULATE (

    SUM ( Sales[Sales Amount] ),

    FILTER ( ALL ( Order Date[Year] ), Order Date[Year] = 2006 )

    )

    VALUES
    返回唯一值,和distinct差不多

    [Sales2006if Selected] :=

    CALCULATE (

    SUM ( Sales[Sales Amount] ),

    FILTER ( VALUES ( Order Date[Year] ), Order Date[Year] = 2006 )

    )



    区别之处,是否计算空值

    Num Of Products := COUNTROWS ( Products )

    Num Of Categories := COUNTROWS ( DISTINCT ( Categories[Product Category Name] ) )

    Num Of Categories Including Missing := COUNTROWS ( VALUES ( Categories[Product Category Name] ) )


    简单时间智能函数

    [Sales] := SUM( Sales[Sales Amount] )

    [Sales MTD] := TOTALMTD ( [Sales], 'Date'[Date] )  月累计至今的销售额

    [Sales QTD] := TOTALQTD ( [Sales], 'Date'[Date] )  季度累计

    [Sales YTD] := TOTALYTD ( [Sales], 'Date'[Date] )  年累计

    也可以这样:

    [Sales MTD] := CALCULATE ( [Sales], DATESMTD ( 'Date'[Date] ) )

    [Sales QTD] := CALCULATE ( [Sales], DATESQTD ( 'Date'[Date] ) )

    [Sales YTD] := CALCULATE ( [Sales], DATESYTD ( 'Date'[Date] ) )


    但更灵活:计算上个周期的数值(周期自定)

    [PY Sales] := CALCULATE ( [Sales], DATEADD ( 'Date'[Date], -1, YEAR ) )

    [PQ Sales] := CALCULATE ( [Sales], DATEADD ( 'Date'[Date], -1, QUARTER ) )

    [PM Sales] := CALCULATE ( [Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )

    [PY Sales] := CALCULATE( [Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )   依然性的

    [PY Total Sales] := CALCULATE( [Sales], PARALLELPERIOD( 'Date'[Date], -1, YEAR ) )  这个不是按自然年度划分的,是指过去12个月为一年,而非去年的现在同期月

    [YTD Over Total PY]:= DIVIDE ( [Sales YTD], [PY Total Sales] )    ,年累计销售额占比过去12个月

             [PY YTD Sales] :=

    CALCULATE (

    [Sales],

    SAMEPERIODLASTYEAR ( DATESYTD ( 'Date'[Date] ) )

    )

    [PY YTD Sales] :=

    CALCULATE(

    [Sales],

    DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

    )


    有点复杂了,嵌套了好几个函数

    [MAT Sales] :=

    CALCULATE (

    [Sales],

    DATESBETWEEN (

    'Date'[Date],

    NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE( 'Date'[Date] ) ) ),

    LASTDATE ( 'Date'[Date] )

    )

    )  
    不太适合,搞不懂

    Units Last Date := CALCULATE ( SUM ( Inventory[Units Balance] ), LASTDATE ( 'Date'[Date] )

    Units Last Non Blank :=

    CALCULATE (

    SUM ( Inventory[Units Balance] ),

    LASTNONBLANK (

    'Date'[Date],

    CALCULATE ( SUM ( Inventory[Units Balance] ) )

    )

    )

    Sales Road650Red44 :=

    CALCULATE (

    SUM ( Sales[Sales Amount] ),

    Products[Product Name] = "Road-650 Red, 44"

    比较不同之处

    Sales Only Road650Red44 :=

    CALCULATE (

    SUM ( Sales[Sales Amount] ),

    Products[Product Name] = "Road-650 Red, 44",

    ALL ( Products ) 

    自定义的年累计

    [YTD] :=

    CALCULATE (

    [Original Measure],

    FILTER (

    ALL ( 'Date' ),

     'Date'[Year] = MAX ( 'Date'[Year] )

    && 'Date'[Date] <= MAX ( 'Date'[Date] )

    )

    )


    有几个客户

    Unique Customers :=

    CALCULATE (

    DISTINCTCOUNT ( Customers[Customer Code] ),

    Sales

    )


    统计函数

    移动平均模板

    Moving Average X 7 Days :=

    AVERAGEX (

    DATESINPERIOD (

    'Date'[Date],

    LASTDATE ( 'Date'[Date] ),

    -7,

    DAY

    ),

    [Total Amount]

    )


    更快速的方法

    Moving Average 7 Days :=

    CALCULATE (

    IF (

    COUNT ( 'Date'[Date] ) >= 7,

    SUM ( Sales[Amount] ) / COUNT ( 'Date'[Date] )

    ),

    FILTER (

    ALL ( 'Date'[Date] ),

    'Date'[Date] > ( MAX ( 'Date'[Date] ) - 7 )

    && 'Date'[Date] <= MAX ( 'Date'[Date] )

    )

    )


    参数表

    [Sales Amount] :=

    IF (

    HASONEVALUE ( Scale[Scale] ),

    SUM ( Sales[Sales Amount] ) / VALUES ( Scale[Scale] ),

    SUM ( Sales[Sales Amount] )

    )   HASONEVALUE 和VALUES合在一起使用,表示选中某一个值

    Discounted Sales Amount :=

    IF (

    HASONEVALUE ( Discounts[Discount Value] ),

    [Sales Amount] * ( 1 – VALUES ( Discounts[Discount Value] ) ),

    IF (

    NOT ( ISFILTERED ( Discounts[Discount] ) ),

    [Sales Amount],

    BLANK ()

    )

    )如果啥都没有选的话就用源值替代

    Top Sales Amount :=

    IF (

    HASONEVALUE ( 'Top'[Top] ),

    IF (

    RANKX (

    ALL ( Sales[Product] ),

    [Sales Amount]

    ) <= VALUES ( 'Top'[Top] ),

    [Sales Amount],

    BLANK ()

    )

    )   带参数表的topN

    计算过去12个月的销售额

    [MAT Sales] :=

    CALCULATE (

    [Sales Amount],

    DATESINPERIOD (

    'Date'[Date],

    LASTDATE ( 'Date'[Date] ),

    -1,

    YEAR

    )

    )


    多选条件下的排名,带切片器的那种

    [Rank by Brand C] :=

    IF (

    HASONEVALUE ( Product[Brand] ),

    RANKX ( ALLSELECTED ( Product[Brand] ), [Sales Amount] )

    )

    相关文章

      网友评论

          本文标题:冲冲冲学DAX

          本文链接:https://www.haomeiwen.com/subject/sfnudxtx.html