DAX Query

MANY to MANY

Product	PRODUCT GROUP CD	FMR
Product Group	PRODUCT_GROUP_CD	PRODUCT_GROUP_CD
		TIME_SPENT_INSTORE_PLAN
MANY		MANY

 

CALCULATE(SUM([TIME_SPENT_INSTORE_PLAN]), SUMMARIZE('Product', PRODUCT_GROUP_CD[PRODUCT_GROUP_CD]))

 

HASONEFILTER & SWITCH

IF(
    HASONEFILTER( Table2[Category] )
    ,SWITCH(
        VALUES( Table2[Category] )
        ,"CostPrice"
        ,[Cost]
        ,"SellingPrice"
        ,[Selling]
        ,"Profit"
        ,[Profit]
    )
    ,[Profit]
)

 

Filter

CALCULATE([Measure], FILTER('Product', 'Product'[Premium] = "Y"))

 

=CALCULATE(VALUES('Product'[PRODUCT_MODEL_ID]), FILTER(ALL('Product'[Product Model]), 'Product'[Product Model] = [PRODUCT_MODEL_CD]))

 

Sum

SUM([column])

 

Count

COUNT([column])

 

Min

MIN([column])

 

Max

MAX([column])

 

DistinctCount The aggregation is a count of unique members

DISTINCTCOUNT([column])

 

None No aggregation is applied

 

ByAccount The system reads the semiadditive behavior specified for the account type

 

AverageOfChildren The aggregation of a member is the average of its children

SUM([column]) / DISTINCTCOUNT([DATE_ID])

 

※ [DATE_ID] = Fact table's Key field (Not Time Dimension)

 

FirstChild The member value is evaluated as the value of its first child along the time dimension

 

LastChild The member value is evaluated as the value of its last child along the time dimension

 

FirstNonEmpty The member value is evaluated as the value of its first child along the time dimension that contains data (현재 컨텍스트를 기준으로 필터링한 column 열에서 식이 비어 있지 않은 마지막 값을 반환합니다)

 

LastNonEmpty The member value is evaluated as the value of its last child along the time dimension that contains data

CALCULATE(SUM([column]), LASTNONBLANK(Time[DateUS], CALCULATE(SUM([column]))))

 

※ [DateUS] = 1/1/2016, 1/2/2016........  

 

PARALLELPELIOD PARALLELPERIOD([Time].[CM Time].[Week],52,[Time].[CM Time].CurrentMember)

CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-1,year))

 

CALCULATE(SUM([column]), FILTER(ALL('Time'), 'Time'[Week] = MAX('Time'[Last52Week])))

 

Avg Avg(PARALLELPERIOD([Time].[CM Time].[Week],3,[Time].[CM Time].CurrentMember):[Time].[CM Time].CurrentMember, [Measures])

 CALCULATE(AVERAGEX(SUMMARIZE('Time', 'Time'[Week], "SS", [Measures]), [SS]), FILTER(ALL('Time'), 'Time'[Week] <= MAX('Time'[Week]) && 'Time'[Week] >= MAX('Time'[Last3Week])))

 

Calculate

CALCULATE(SUM([column]), FILTER('Product', 'Product'[Premium] = "Y")

 

Index Column

=CALCULATE(COUNT([Date]), ALL(‘Date’), FILTER(‘Date’, [Date]<=EARLIER([Date])))

 

 

SELECT ROW_NUMBER() OVER (ORDER BY PRODUCT_MODEL_CD) AS Row, FROM [dbo].[]