Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Hi,
You may download my PBI file from here.
Hope this helps.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |