How to get results based on Left Join logic

[kamran]

Hi all

I've tried to produce results in a Matrix visualization using all possible ways I know about DAX, i.e RELATED, CROSSFILTER, NATURALLEFTOUTERJOIN and FILTER but unable to produce a Left-Joined result set.

My tables are joined with each other with Many-to-One relationship with each other based on common column names.

Though apparently looks quite a simple piece of work but I don't know what's stopping it.

When I'm applying the RELATED, CROSSFILTER etc, though it shows the Sold Quantitiy correctly but it ignores the Products not sold.

And when I omit the relationship functions, it displays all the products but displays Total Sales figure.

Can you please give me a solution for this.

My simplified form of data and wish list is as below:

LookupProductCategories
Product Category ID	Category Name
1	LAPTOP
2	Tablet
3	Cell Phone

LookupProduct
Product ID	Product Category ID	Product Name
1.1	1	DEL LAPTOP
1.2	1	HP LAPTOP
1.3	1	Microsoft LAPTOP
2.1	2	iPad
2.2	2	Samsung S3
3.1	3	Nokia Phone
3.2	3	Samsung Phone
3.3	3	Huawei Phone
3.4	3	iPhone

Sales
Product ID	Quantity
1.1	1
1.2	3
2.1	2
3.1	5
3.2	11

Result Set Needed
Product ID	Quantity Sold
1.1	1
1.2	3
1.3	0
2.1	2
2.2	0
3.1	5
3.2	11
3.3	0
3.4	0

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[kamran]

Hi Ashish

Thanks for the help, you'r almost there, apologies that I missed to add a key thing that is:

please ignore the SUM of Quanitity and try to do: [DistinctCount of Comments].

I need Categories in the Matrix columns, Product in the Rows, and their respective [DistinctCount of Comments], as below:

It's not letting me to do Count of Comments over Category & Product ID Where Comments Is Not Null ( <> "" OR NOT(ISBLANK() ).

Sales
Product ID	Category	Quantity	Comments
1.1	1	1	ABC
1.2	1	3	XYZ
2.1	2	2	JKL
3.1	3	5
3.2	3	11	MNO
1.1	1	4
3.1	3	6	ABC
2.1	2	5
1.2	1	8	XYZ
3.1	3	1	ABC
3.2	3	7	PQR

Result
	Distinct Count of Comments
Categories / Product ID	1	2	3	Total
1.1	1			1
1.2	1			1
2.1		1		1
3.1			1	1
3.2			2	2
Total	2	1	3	6

Alternate SQL could be like:

Select Cat.[Category ID], Prd.[Product ID], 
Count(distinct Sal.[Comments]) TotalComments
From dbo.Category Cat
Inner Join dbo.Product Prd On
	Prd.[Category ID] = Cat.[Category ID]
Left Join dbo.Sales Sal On
	Sal.[Product ID] = Prd.[Product ID]
Group By Cat.[Category ID], Prd.[Product ID]

Thanks

[v-shex-msft]

HI @kamran,

You can try to use following measure to calculate non blank distinct comment count:

NONBlank Count = COUNTA(Sales[Comments])-COUNTBLANK(Sales[Comments])

Relationship map:

Regards,

Xiaoxin Sheng