BISM-实用的DAX直接查询

以下是一些DAX的直接查询，比较实用。

FILTER RESULT

EVALUATE ( 
filter(
summarize( 'Internet Sales' 
,'Date'[Calendar Year] 
,'Product Category'[Product Category Name] 
,"SumofInternetSalesUnitPrice" 
,FORMAT(SUM('Internet Sales'[Unit Price]),"currency") 
)
, 'Date'[Calendar Year] = 2007
)
)

FORMAT RESULT

EVALUATE (
summarize( 'Internet Sales' 
,'Product Category'[Product Category Name]
,"SumofInternetSalesUnitPrice" 
,FORMAT(SUM('Internet Sales'[Unit Price]),"currency")
)
)

CROSSJOIN

EVALUATE (
summarize(
crossjoin ( 
//values('Product'[Product Name]),
values('Product Category'[Product Category Name]),
values('Internet Sales'[Unit Price]) 
) 
// ,'Product'[Product Name]
,'Product Category'[Product Category Name] 
,'Internet Sales'[Unit Price] 
)
)

 

ROW

EVALUATE
row( "Product TableCount",
countrows('Product'),
"Internet Sales TableCount",
countrows('Internet Sales')
)

 

FILTER AND ORDER

EVALUATE
( 
filter (
summarize(
'Internet Sales',
'Internet Sales'[Order Date],
'Internet Sales'[Ship Date],
'Internet Sales'[Unit Price]
)
, 'Internet Sales'[Unit Price] > 3500
)
)
ORDER BY 'Internet Sales'[Order Date] DESC

 

TOPN

DEFINE MEASURE'Geography'[Sales] = sum('Internet Sales'[Sales Amount])
EVALUATE (
addcolumns(
topn(
2
,values(Geography[Country Region Name])
, 'Geography'[Sales]
, 0
)
,"SalesAmount"
, format('Geography'[Sales],"currency")
)
)
order by 'Geography'[Sales] desc

 

TOPN AND GENERATE

DEFINE MEASURE'Geography'[Sales] = sum('Internet Sales'[Sales Amount])
EVALUATE (
addcolumns(
GENERATE(
values('Geography'[Country Region Name])
, topn(
2 
, values('Geography'[City])
, 'Geography'[Sales]
, 0
) 
)
,"SalesAmount"
, format('Geography'[Sales],"currency")
)
)
order by 
'Geography'[Country Region Name] asc
, 'Geography'[Sales] desc

 

USERELATIONSHIP

define 
measure 'Internet Sales'[InternetDueSales] = calculate(sum('Internet Sales'[Sales Amount]) , userelationship('Date'[DateKey],'Internet Sales'[DueDateKey]))

measure 'Internet Sales'[InternetOrderSales] = calculate(sum('Internet Sales'[Sales Amount]) , userelationship('Date'[DateKey],'Internet Sales'[OrderDateKey]))

measure 'Internet Sales'[InternetShipSales] = calculate(sum('Internet Sales'[Sales Amount]) , userelationship('Date'[DateKey],'Internet Sales'[ShipDateKey]))

EVALUATE(
addcolumns( 
values('Date'[Calendar Year]) 
,"InternetDueSales"
, format('Internet Sales'[InternetDueSales],"currency")
, "InternetOrderSales"
, format('Internet Sales'[InternetOrderSales],"currency")
, "InternetShipSales"
, format('Internet Sales'[InternetShipSales],"currency")
)
)

 

PARALLELPERIOD

define 
measure 
'Internet Sales'[PresentYearInternetSales] = 
calculate(sum('Internet Sales'[Sales Amount]))

measure 
'Internet Sales'[PrevSales] = 
calculate(sum('Internet Sales'[Sales Amount]),parallelperiod('Date'[Date], -1, year))

measure 
'Internet Sales'[Variance] = 
'Internet Sales'[PresentYearInternetSales] - 'Internet Sales'[PrevSales]

measure 
'Internet Sales'[PercentVariance] = 
IF( ISBLANK('Internet Sales'[PrevSales]),BLANK(),('Internet Sales'[Variance]/'Internet Sales'[PrevSales]))

EVALUATE (
SUMMARIZE ( 'Internet Sales'
,'Date'[Calendar Year]
,"PresentYearInternetSales"
,format('Internet Sales'[PresentYearInternetSales],"currency")
,"PrevYearInternetSales"
, format('Internet Sales'[PrevSales],"currency")
,"Variance"
,format('Internet Sales'[Variance],"currency")
,"PercentageVariance"
,format('Internet Sales'[PercentVariance],"percent")
) 
)

 

RUNNINGTOTAL

define measure 'Internet Sales'[MTDAmount] = TOTALMTD(sum('Internet Sales'[Sales Amount]),'Date'[Date])
measure 'Internet Sales'[QTDAmount] = TOTALQTD(sum('Internet Sales'[Sales Amount]),'Date'[Date])
measure 'Internet Sales'[YTDAmount] = TOTALYTD(sum('Internet Sales'[Sales Amount]),'Date'[Date])
EVALUATE (
	 FILTER (
	   SUMMARIZE(
				'Internet Sales'
				, 'Date'[Date]
				, 'Date'[Calendar Year]
				, "Internet Sales"
				, FORMAT(SUM('Internet Sales'[Sales Amount]),"currency")
				, "Sales MTD"
				,FORMAT('Internet Sales'[MTDAmount],"currency")
				, "Sales QTD"
				,FORMAT('Internet Sales'[QTDAmount],"currency")
				, "Sales YTD"
				,FORMAT('Internet Sales'[YTDAmount],"currency")
				 )
			 , 'Date'[Calendar Year] = 2008
			 )
        )
		ORDER BY 'Date'[Date] ASC

 

RANK OVER TIME

define 
measure 'Internet Sales'[PrevSales] = 
      calculate(sum('Internet Sales'[Sales Amount]),parallelperiod('Date'[Date], -1, year))
measure 'Date'[Ranks] = RANKX(ALL('Date'[Calendar Year]),SUMX(RELATEDTABLE('Internet Sales'),[Sales Amount]))
measure 'Date'[PrevRanks] = IF(ISBLANK('Internet Sales'[PrevSales]),BLANK(),RANKX(ALL('Date'[Calendar Year]),'Internet Sales'[PrevSales]))
EVALUATE (
SUMMARIZE ('Internet Sales'
			,'Date'[Calendar Year]
			,"PresentYearRank"
			,'Date'[Ranks]
			,"InternetSales"
			,format(sum('Internet Sales'[Sales Amount]),"currency")
			,"PreviousYearRank"
			,'Date'[PrevRanks]
			,"PrevYearInternetSales"
			, format('Internet Sales'[PrevSales],"currency")
           ) 
         
order by 'Date'[Ranks]

 

RANK ONE LOGIC

define 
measure 'Internet Sales'[PrevSales] = 
      calculate(sum('Internet Sales'[Sales Amount]),parallelperiod('Date'[Date], -1, year))
measure 'Date'[Ranks] = RANKX(ALL('Date'[Calendar Year]),SUMX(RELATEDTABLE('Internet Sales'),[Sales Amount]))
measure 'Date'[PrevRanks] = IF(ISBLANK('Internet Sales'[PrevSales]),BLANK(),RANKX(ALL('Date'[Calendar Year]),'Internet Sales'[PrevSales]))
EVALUATE (
SUMMARIZE ('Internet Sales'
			,'Date'[Calendar Year]
			,"PresentYearRank"
			,'Date'[Ranks]
			,"InternetSales"
			,format(sum('Internet Sales'[Sales Amount]),"currency")
			,"PreviousYearRank"
			,'Date'[PrevRanks]
			,"PrevYearInternetSales"
			, format('Internet Sales'[PrevSales],"currency")
           ) 
         
order by 'Date'[Ranks]

 

RANK NESTED LOGIC

DEFINE MEASURE'Geography'[Sales] = sum('Internet Sales'[Sales Amount])
measure 'Geography'[CityRanks] = RANKX(ALL('Geography'[City]),SUMX(RELATEDTABLE('Internet Sales'),[Sales Amount]))
EVALUATE (
 addcolumns(		
			GENERATE(
				values('Geography'[Country Region Name])
					, topn(
						  2	
							,  values('Geography'[City])
							, 'Geography'[Sales]
						, 0
						 )						
			    )								
			,"CityRanks"
			, 'Geography'[CityRanks]
			,"SalesAmount"
			, format('Geography'[Sales],"currency")
			)
	     )
		order by 'Geography'[Country Region Name] asc
		, 'Geography'[Sales] desc