cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamran Frequent Visitor
Frequent Visitor

How to get results based on Left Join logic

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 IDCategory Name
1LAPTOP
2Tablet
3Cell Phone

 

 

LookupProduct  
Product IDProduct Category IDProduct Name
1.11DEL LAPTOP
1.21HP LAPTOP
1.31Microsoft LAPTOP
2.12iPad
2.22Samsung S3
3.13Nokia Phone
3.23Samsung Phone
3.33Huawei Phone
3.43iPhone

 

Sales 
Product IDQuantity
1.11
1.23
2.12
3.15
3.211

 

Result Set Needed 
Product IDQuantity Sold
1.11
1.23
1.30
2.12
2.20
3.15
3.211
3.30
3.40
3 REPLIES 3
Super User
Super User

Re: How to get results based on Left Join logic

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

kamran Frequent Visitor
Frequent Visitor

Re: How to get results based on Left Join logic

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 IDCategoryQuantityComments
1.111ABC
1.213XYZ
2.122JKL
3.135 
3.2311MNO
1.114 
3.136ABC
2.125 
1.218XYZ
3.131ABC
3.237PQR

 

 

Result    
 Distinct Count of Comments 
Categories / Product ID123Total
1.11  1
1.21  1
2.1 1 1
3.1  11
3.2  22
Total2136

 

 

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

Community Support Team
Community Support Team

Re: How to get results based on Left Join logic

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:

7.png

 

8.png

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |