登录查看更多内容

Dax trong USS (Unified star schema)

Nghia Tran

Solutions Consultant at CMC

发布日期: 2022年11月22日

S? d?ng m? hình USS trong Power BI yêu c?u s? d?ng DAX ph?c t?p h?n so v?i m? hình dimension th?ng th??ng. Tùy vào tr??ng h?p mà ta s? quy?t ??nh vi?t nh? th?: d? hi?u, hi?u n?ng, yêu c?u t?i ?a,... . Th??ng s? có 3 cách: s? d?ng các hàm có h?u t? X ?? truy v?n th?ng qua Bridge, dùng CROSSFILTER ?? ??o chi?u filter Bridge -> Table, TREATAS ?? filter t? Bridge -> table, SUMMARIZE ?? t?o temp table ?? truy v?n (distinctcountx).

Ví d? demo

?? demo t?i s? d?ng AdventureWorksDW Link, load 4 table: FactInternetSale, Date, Product, Customer. R?i Union 4 b?ng này vào Bridge, phan bi?t b?ng c?t Stage.

No alt text provided for this image — m? hình

Kích th??c c?a Dataset trên Power BI Desktop x?p x? 400 Mb (nh? phù h?p ?? demo)

Các hàm th?ng th??ng s? d?ng

1) Hàm SUM ; ???c s? d?ng nhi?u nh?t trong truy v?n; th??ng dùng ?? tính t?ng giá tr? ??n hàng, t?ng d? n?, ...

Cách 1: truy v?n th?ng qua Bridge


$Sales Amount 1= SUMX(Bridge, RELATED('InternetSales'[SalesAmount]))

Truy v?n

// DAX Query
DEFINE
??VAR __DS0FilterTable =?
????TREATAS({"M"}, 'Customer'[Gender])

EVALUATE
SUMMARIZECOLUMNS(
??????'Product'[Color],
??????__DS0FilterTable,
??????"v_Order_Sales_Amount", 'InternetSales'[$Sales Amount 1]
????)

xSQL ???c t?o ra: nhanh ~ 34 ms


SELECT
'Product'[Color],
SUM ( 'InternetSales'[SalesAmount] )
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
	LEFT OUTER JOIN 'InternetSales' ON 'Bridge'[ KEY InternetSales]='InternetSales'[ KEY InternetSales]
WHERE
	'Customer'[Gender] = 'M';

Cách 2: truy v?n b?ng CROSSFILTER

$Sales Amount 2 = 
CALCULATE(
SUM('InternetSales'[SalesAmount])
, CROSSFILTER(Bridge[_KEY_InternetSales], InternetSales[_KEY_InternetSales]
,Both
))

Truy v?n: nh? cách 1

xSQL: Th?i gian ~2s t?o nhi?u b?ng t?m ?? truy v?n bên d??i SE, nh?ng hi?u n?ng t?

--$TTable3
DEFINE TABLE '$TTable3' := SELECT
'Product'[Color], 'InternetSales'[ KEY InternetSales]
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
	LEFT OUTER JOIN 'InternetSales' ON 'Bridge'[ KEY InternetSales]='InternetSales'[ KEY InternetSales]
WHERE
	'Customer'[Gender] = 'M';

--$TTable4
DEFINE TABLE '$TTable4' := SELECT
SIMPLEINDEXN ( '$TTable3'[InternetSales$ KEY InternetSales] )
FROM '$TTable3';

--$TTable1
DEFINE TABLE '$TTable1' := SELECT
'$TTable3'[Product$Color],
SUM ( '$TTable2'[$Measure0] )
FROM '$TTable2'
	INNER JOIN '$TTable3' ON '$TTable2'[InternetSales$ KEY InternetSales]='$TTable3'[InternetSales$ KEY InternetSales]
REDUCED BY
'$TTable2' := SELECT
'InternetSales'[ KEY InternetSales],
SUM ( 'InternetSales'[SalesAmount] )
FROM 'InternetSales'
WHERE
	'InternetSales'[ KEY InternetSales] ININDEX '$TTable4'[$SemijoinProjection];

Cách 3: s? d?ng TREATAS

$Sales Amount 3 =
CALCULATE(
? ? SUM('InternetSales'[SalesAmount])
? ? ,TREATAS( VALUES(Bridge[_KEY_InternetSales]), InternetSales[_KEY_InternetSales])
)

Truy v?n: nh? cách 1

xSQL: th?i gian ~4s t?o 5 query truy v?n

2) Hàm DISTINCTCOUNT - > DISTINCTCOUNTX ( ^^ làm gì có): s? d?ng ?? ??m s? l??ng Unique khách hàng, s?n ph?m, ...

Cách 1: tr?c ti?p truy v?n vào Bridge; s? d?ng Stage ?? bi?t mình mu?n ??m d?a vào b?ng nào.

#Customer 1 =
CALCULATE(
? ? DISTINCTCOUNT(Bridge[_KEY_Customer])
? ? ,KEEPFILTERS('Bridge'[Stage] = "InternetSales")
)

Truy v?n:

// DAX Query
DEFINE
??VAR __DS0FilterTable =?
????TREATAS({"M"}, 'Customer'[Gender])
??VAR __DS0Core =?
????SUMMARIZECOLUMNS('Product'[Color], __DS0FilterTable, "v_Customer_1", 'InternetSales'[#Customer 1])
EVALUATE
??__DS0Core

xSQL:


SELECT
'Product'[Color],
DCOUNT ( 'Bridge'[ KEY Customer] )
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
WHERE
	'Customer'[Gender] = 'M' VAND
	'Bridge'[Stage] = 'InternetSales';

Cách 2: Truy v?n t? Bridge ra b?ng khác

#SalesOrderNumber= 
CALCULATE(
? ? COUNTROWS ( 
? ? ? ? DISTINCT ( 
? ? ? ? ? ? SELECTCOLUMNS ( 
? ? ? ? ? ? ? ? SUMMARIZE(
? ? ? ? ? ? ? ? ? ? 'Bridge',InternetSales[SalesOrderNumber]
? ? ? ? ? ? ? ? ? ? )
? ? ? ? ? ? ? ? ? ? , "Col"
? ? ? ? ? ? ? ? ? ? , 'InternetSales'[SalesOrderNumber] 
? ? ? ? ? ? ? ? ) 
? ? ? ? ? ? )
? ? ? ? )
,NOT ISBLANK(InternetSales[SalesOrderNumber])
)

Truy v?n:

DEFIN
??VAR __DS0FilterTable =?
????TREATAS({"M"}, 'Customer'[Gender])

??VAR __DS0Core =?
????SUMMARIZECOLUMNS(
??????'Product'[Color],
??????__DS0FilterTable,
??????"v_SalesOrderNumber_2", 'InternetSales'[#SalesOrderNumber 2]
????)
EVALUATE
??__DS0CoreE

xSQL:

SELECT
'Product'[Color],
DCOUNT ( 'InternetSales'[SalesOrderNumber] )
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
	LEFT OUTER JOIN 'InternetSales' ON 'Bridge'[ KEY InternetSales]='InternetSales'[ KEY InternetSales]
WHERE
	'Customer'[Gender] = 'M' VAND
	'InternetSales'[SalesOrderNumber] NIN ( null )

Cách 3: s? d?ng CROSSFILTER

#SalesOrderNumber 3 =
CALCULATE(
? ? DISTINCTCOUNT(
? ? ? ? 'InternetSales'[SalesOrderNumber]
? ? ),
? ? CROSSFILTER(Bridge[_KEY_InternetSales],InternetSales[_KEY_InternetSales], Both)
)

Truy v?n: gi?ng cách 2

领英推荐

A hidden little gem that can save your (Power BI) life!

Nikola Ilic 2 年前

Understanding Explicit vs Implicit measures in Power BI

Nikola Ilic 2 年前

Dependent Names

Rainer Grimm 3 年前

xSQL: ~3s hi?u n?ng b? gi?m m?nh khi FE t?o ra nhi?u query truy v?n

SELECT
'Product'[Color], 'InternetSales'[ KEY InternetSales]
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
	LEFT OUTER JOIN 'InternetSales' ON 'Bridge'[ KEY InternetSales]='InternetSales'[ KEY InternetSales]
WHERE
	'Customer'[Gender] = 'M';

T?o ra r?t thêm 9 cau query n?a cho t?ng màu s?c:
SELECT
 DCOUNT ( 'InternetSales'[SalesOrderNumber] )
FROM 'InternetSales'
WHERE
	'InternetSales'[ KEY InternetSales] IN ( 25905, 26903, 30895, 29897, 27901, 32891, 59796, 20915, 18919, 54806..[30382 total values, not all displayed] ) VAND
	'InternetSales'[ KEY InternetSales] IN ( 26903, 7941, 54806, 51812, 47820, 8898, 6902, 30854, 46781, 44785..[4997 total values, not all displayed]?
..

Cách 4: s? d?ng SUMX

#SalesOrderNumber 4 =
SUMX
(
? ? CALCULATETABLE(
? ? ? ? SUMMARIZE(
? ? ? ? ? ? ? ? 'Bridge',
? ? ? ? ? ? InternetSales[SalesOrderNumber]
? ? ? ? )
? ? ? ? , NOT ISBLANK(InternetSales[SalesOrderNumber])
? ? )
? ? ,
? ? 1
)

Truy v?n: gi?ng cách 2

xSQL: ~93ms T?o b?ng t?m group by giá tr? l?i r?i ??y FE ?? tính toán ti?p.


SET DC_KIND="AUTO"
SELECT
'Product'[Color], 'InternetSales'[SalesOrderNumber]
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
	LEFT OUTER JOIN 'InternetSales' ON 'Bridge'[ KEY InternetSales]='InternetSales'[ KEY InternetSales]
WHERE
	'Customer'[Gender] = 'M' VAND
	'InternetSales'[SalesOrderNumber] NIN ( null ) ;

Cách 4: s? d?ng TREATAS

#SalesOrderNumber 5 =
CALCULATE(
? ? DISTINCTCOUNT(
? ? ? ? 'InternetSales'[SalesOrderNumber]
? ? ),
? ? TREATAS(VALUES(Bridge[_KEY_InternetSales]),InternetSales[_KEY_InternetSales])
)

Truy v?n: gi?ng cách 2

xSQL: ~4s, FE t?o ra quá nhi?u query.

ELECT
'Product'[Color], 'Bridge'[ KEY InternetSales]
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
WHERE
	'Customer'[Gender] = 'M';
--
SELECT DCOUNT ( 'InternetSales'[SalesOrderNumber] )
FROM 'InternetSales'
WHERE	'InternetSales'[ KEY InternetSales] IN ( 4520352, 1828075, 3556475, 2791952, 4620029, 5484229, 199352, 299029, 1063552, 3755829..[3038101 total values, not all displayed] ) VAND	'InternetSales'[ KEY InternetSales] IN ( 1828075, 3556475, 3755829, 3855506, 5882937, 1262906, 3955183, 3290337, 2326460, 2226783..[1466000 total values, not all displayed] ) ;

3) ??m s? l??ng b?n ghi COUNT - > COUNTX ( ho?c cái khác )

Cách 1: s? d?ng tr?c ti?p Bridge, dùng SUMX

#Sale Rows 1 =
CALCULATE(
? ? SUMX( 'Bridge', 1)
? ? , Bridge[Stage] = "InternetSales"
)

Truy v?n:

// DAX Quer
DEFINE
??VAR __DS0FilterTable =?
????TREATAS({"M"}, 'Customer'[Gender])
??VAR __DS0Core =?
????SUMMARIZECOLUMNS('Product'[Color], __DS0FilterTable, "v_Sale_Rows_1", 'InternetSales'[#Sale Rows 1])
EVALUATE
__DS0Corey

xSQL:

WITH
	$Expr0 := 1
SELECT
'Product'[Color],
SUM ( @$Expr0 )
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
WHERE
	'Customer'[Gender] = 'M' VAND
	'Bridge'[Stage] = 'InternetSales';

Cách 2: s? d?ng COUNTX th?ng qua bridge

#Sale Rows 2 = 
CALCULATE(
? ? countx( 
? ? ? ? 'Bridge', 
? ? ? ? RELATED(InternetSales[_KEY_InternetSales])
? ? )
? ? ,Bridge[Stage] = "InternetSales"
)

Truy v?n: t??ng t? 1

xSQL:

SELECT
'Product'[Color],
COUNT (? )
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
	LEFT OUTER JOIN 'InternetSales' ON 'Bridge'[ KEY InternetSales]='InternetSales'[ KEY InternetSales]
WHERE
	'Customer'[Gender] = 'M' VAND
	'Bridge'[Stage] = 'InternetSales' VAND
	'InternetSales'[ KEY InternetSales] IS NOT NULL;

Cách 3: s? d?ng CROSSFILTER

#Sale Rows 3 =
CALCULATE(
? ? count( 
? ? ? ?InternetSales[_KEY_InternetSales]
? ? )
? ? ,CROSSFILTER(Bridge[_KEY_InternetSales], InternetSales[_KEY_InternetSales],Both)

Truy v?n: t??ng t? 1

xSQL:


4 Query, 3 query, 1 còn l?i g?p e query l?i

#1
DEFINE TABLE '$TTable3' := SELECT
'Product'[Color], 'InternetSales'[ KEY InternetSales]
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
	LEFT OUTER JOIN 'InternetSales' ON 'Bridge'[ KEY InternetSales]='InternetSales'[ KEY InternetSales]
WHERE
	'Customer'[Gender] = 'M';

#2
DEFINE TABLE '$TTable4' := SELECT
SIMPLEINDEXN ( '$TTable3'[InternetSales$ KEY InternetSales] )
FROM '$TTable3';

#3
DEFINE TABLE '$TTable1' := SELECT
'$TTable3'[Product$Color],
SUM ( '$TTable2'[$Measure0] )
FROM '$TTable2'
	INNER JOIN '$TTable3' ON '$TTable2'[InternetSales$ KEY InternetSales]='$TTable3'[InternetSales$ KEY InternetSales]
REDUCED BY
'$TTable2' := SELECT
'InternetSales'[ KEY InternetSales],
SUM (? ( PFDATAID ( 'InternetSales'[ KEY InternetSales] ) <> 2 )? )
FROM 'InternetSales'
WHERE
	'InternetSales'[ KEY InternetSales] ININDEX '$TTable4'[$SemijoinProjection];

Cách 4: dùng TREATAS

#Sale Rows 4 =
CALCULATE(
? ? count( 
? ? ? ?InternetSales[_KEY_InternetSales]
? ? )
? ? ,TREATAS(VALUES(Bridge[_KEY_InternetSales]), InternetSales[_KEY_InternetSales])
)

Truy v?n: t??ng t? 1

xSQL:

1
DEFINE TABLE '$TTable3' := SELECT
'Product'[Color], 'Bridge'[ KEY InternetSales]
FROM 'Bridge'
	LEFT OUTER JOIN 'Product' ON 'Bridge'[ KEY Product]='Product'[ KEY Product]
	LEFT OUTER JOIN 'Customer' ON 'Bridge'[ KEY Customer]='Customer'[ KEY Customer]
WHERE
	'Customer'[Gender] = 'M';
#2
DEFINE TABLE '$TTable4' := SELECT
'$TTable3'[Bridge$ KEY InternetSales]
FROM '$TTable3';
#3
DEFINE TABLE '$TTable5' := SELECT
RJOIN ( '$TTable4'[Bridge$ KEY InternetSales] )
FROM '$TTable4'
	REVERSE BITMAP JOIN 'InternetSales' ON '$TTable4'[Bridge$ KEY InternetSales]='InternetSales'[ KEY InternetSales];
#4

DEFINE TABLE '$TTable1' := SELECT
'$TTable3'[Product$Color],
SUM ( '$TTable2'[$Measure0] )
FROM '$TTable2'
	INNER JOIN '$TTable3' ON '$TTable2'[InternetSales$ KEY InternetSales]='$TTable3'[Bridge$ KEY InternetSales]
REDUCED BY
'$TTable2' := SELECT
'InternetSales'[ KEY InternetSales],
SUM (? ( PFDATAID ( 'InternetSales'[ KEY InternetSales] ) <> 2 )? )
FROM 'InternetSales'
WHERE
	'InternetSales'[ KEY InternetSales] ININDEX '$TTable5'[$SemijoinProjection];

K?t lu?n

?a ph?n các tình hu?ng s? d?ng các hàm X ?em l?i hi?u n?ng t?t nh?t. Nh?ng 1 s? tr??ng h?p kh?ng th? d?ng thì chúng ta v?n lu?n có cách ?? hoàn thành c?ng vi?c. Queryplan cho MIN, MAX thì t??ng t? SUM nên t?i kh?ng có ??a ra s? li?u ? ?ay.

Link file demo Download

要查看或添加评论，请登录

Nghia Tran的更多文章

Microsoft Fabric Licensing Calculator

2023年11月29日

Microsoft Fabric Licensing Calculator

Updated image Full Link
RLS in Microsoft Fabric Dataset

2023年10月10日

RLS in Microsoft Fabric Dataset

Some limitations of Direct Lake in its current Public Preview state include the inability to create row-level security…
Real-time Analytics: Streaming SQL Server Data to Fabric using Debezium

2023年9月28日

Real-time Analytics: Streaming SQL Server Data to Fabric using Debezium

Introduction/What This article draws a method to stream data in real-time from SQL Server to Fabric using Debezium…
Power BI Custom Connector: Build a Module's Lineage View

2023年8月9日

Power BI Custom Connector: Build a Module's Lineage View

What The scenario we are dealing with is as follows: Power Query connectors do not support Lineage View in Visual…

4 条评论
DBT in Microsoft Fabric: MY JOURNAL

2023年6月27日

DBT in Microsoft Fabric: MY JOURNAL

Update 20231114: Git Installs on Runtime 1.2 (link) - thanks venkims fromMS.

2 条评论
Microsoft Fabric With dbt

2023年6月16日

Microsoft Fabric With dbt

UPDATE 2023-06-28: You can find more complete detailed and optimized examples for this content in the DBT in Microsoft…
Dynamic format strings in Power BI

2023年4月13日

Dynamic format strings in Power BI

V?n ?? Các báo cáo tài chính c?a Vi?t Nam th??ng có con s? r?t l?n t? ??n vài nghìn t?, do v?y h? th??ng có nhu c?u…

1 条评论
Load Azure to Snowflake by dbt

2023年4月12日

Load Azure to Snowflake by dbt

V?n ?? Sau khi d? li?u ???c trích xu?t t? các Datasource và ???c ?áp xu?ng Azure Data Lake. Ta c?n ??a d? li?u t? Lake…

1 条评论
Camera AI và Power BI ?? theo d?i k? n?ng c?a nhan viên real-time.

2023年4月4日

Camera AI và Power BI ?? theo d?i k? n?ng c?a nhan viên real-time.

M?c tiêu Data Team xay d?ng 1 ??a ?i?m ?? nhan viên có th? xem th?ng tin k? n?ng mà mình ?? h?c khi tham gia các l?p…
Auto refresh data in Power BI Desktop

2023年4月4日

Auto refresh data in Power BI Desktop

Power BI Desktop là phiên b?n Free cho phép chúng ta k?t n?i/x? ly/ v? bi?u ?? chia s? file ??n cho ng??i khác. Nh?ng…

3 条评论

See all articles

Dax trong USS (Unified star schema)

Nghia Tran

Solutions Consultant at CMC

领英推荐

Nghia Tran的更多文章

社区洞察

其他会员也浏览了

Multi-Color Gradient with DAX in Power BI

The Great Data Union: Power BI's Merge or Append Comedy Show!

Tableau : How to create highlight in viz in tooltips

Navigating Excel's Quirks and Limitations ??

Rebinding Power BI Reports to Different Dataset

Power BI Challenge #9 Wrap Up

Analyzing WhatsApp Group “Squad Tenaga” Chats with Power BI

Transforming Data Across SQL, DAX, and Excel

领英推荐

Nghia Tran的更多文章

Microsoft Fabric Licensing Calculator

RLS in Microsoft Fabric Dataset

Real-time Analytics: Streaming SQL Server Data to Fabric using Debezium

Power BI Custom Connector: Build a Module's Lineage View

DBT in Microsoft Fabric: MY JOURNAL

Microsoft Fabric With dbt

Dynamic format strings in Power BI

Load Azure to Snowflake by dbt

Camera AI và Power BI ?? theo d?i k? n?ng c?a nhan viên real-time.

Auto refresh data in Power BI Desktop

社区洞察

其他会员也浏览了

Multi-Color Gradient with DAX in Power BI

The Great Data Union: Power BI's Merge or Append Comedy Show!

Tableau : How to create highlight in viz in tooltips

Navigating Excel's Quirks and Limitations ??

Rebinding Power BI Reports to Different Dataset

Power BI Challenge #9 Wrap Up

Analyzing WhatsApp Group “Squad Tenaga” Chats with Power BI

Transforming Data Across SQL, DAX, and Excel