DAX – RelatedTable Scenario

ByHariharan Rajendran

DAX – RelatedTable Scenario

There is a scenario to find the total count of vendor based on the current year and the same customer should exist on the main transaction table.

Let me explain in detail.

Consider, I have a table called vendors which have very simple columns like below.

Vendor ID Vendor Name Created Date
101 V1 Wednesday, February 22, 2017
102 V2 Thursday, December 28, 2017
103 V3 Friday, February 2, 2018
104 v4 Monday, May 7, 2018

 

And Transaction table like below.

Trans ID Vendor ID QTY Sale Amount OrderDate
1 101 5 1500 Tuesday, August 22, 2017
2 101 6 2588 Saturday, December 30, 2017
3 102 8 4500 Monday, April 2, 2018
4 103 12 8000 Thursday, June 7, 2018

 

On the above tables, I want to show the total count of vendors where they are available in the transaction table and their created date should be the current year.

The expected result is “1” as Vendor 103 only available in Transaction table and created date is the current year 2018.

Rest of the rows are not matched with this scenario.

There are various methods are available to achieve the solution. In this post, I am explaining the very simple method.

Step 1: Create a new calculated column on the table Vendors using below script.

IsMatch = IF(Format(TODAY(),”YYYY”) = FORMAT(‘Customer (2)'[Date],”YYYY”), “Match”, “NoMatch”)

It is adding a new column with values like match and nomatch.

 

Step 2: Create another calculated column on the vendor table using below script. We need to use the above-created column.

Count = CALCULATE(IF(ISBLANK(MINX(RELATEDTABLE(‘FACT’),”1″)),0,1),’Customer (2)'[Test]=”Match”)

That’s it. We can use the count column for the result.

 

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!