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.
We have the following data structure:
One fact table with OrderLines where 1product per order = 1 row. And 1 order number >= 1 row (for each product in the order another row is created). We do not have a parent (dimmension) table for Orders, just the numbers in the column OrderNo.
One dimmension table with Products.
The user wants to be able to select and visualize the orders that contain 2 products based on his selection (slicer).
Since we have a lot of rows in the db and a lot of possible orderlines with different products/order we would like to use the basic structure and do this calculation in DAX, based on 2 slicer selections from the dim Products table. We can afford to add another Product table (as dimmension table), but nothing else in the fact table.
You can see the demo report here: https://drive.google.com/file/d/1fdRrs587_mKA31GwiWx0pikqzsbnsi5l/view?usp=sharing
- As you can see order 1001 has both product 1 and 2. the user should select 1 in a slicer, then select 2 in another slicer (or in the same slicer if that is possible) and then visualize the orders that contain both products.
Thank you!
Solved! Go to Solution.
@Anonymous , use this measure with the order number in the visual
measure =
var _cnt = countx(allselected(Products),products[productID])
return
countx(filter(summarize(Orderlines, Orderlines[OrderNo], "_1", distinctcount(Orderlines[productID])),[_1]=_cnt),[OrderNo]
@Anonymous , use this measure with the order number in the visual
measure =
var _cnt = countx(allselected(Products),products[productID])
return
countx(filter(summarize(Orderlines, Orderlines[OrderNo], "_1", distinctcount(Orderlines[productID])),[_1]=_cnt),[OrderNo]
@amitchandak thank you very much for your answer.
Indeed it works great, but I just realized that I need to be able to select based on category too. I have added a ProductCategory column in the Products table. I have tried to adapt the DAX but it didn't work.
This means that if the category 1 and category 2 are selected and there are:
- 1 order with prod1 from cat 1 / prod 3 from cat 2
- 1 order with prod 2 from cat 1 / prod 4 from cat 2
The result should be 2 orders. But since PBI is expecting to see orders with all 4 products (prod 1,2,3,4) at the same time it gives me blank.
I have modified the data source and the PBI report.
Now the measure should have the value 3 in the example below. You can find the report here:
https://drive.google.com/file/d/1y2Qw-9ZDsINk4suMh9JysKoLTgFAjifD/view?usp=sharing
Thank you in advance!
@amitchandakbased on your solution I managed to design this query and everything works as expected now. Thank you very much!
Orders with all selected categories =
VAR _cnt =
COUNTROWS (
DISTINCT (
SELECTCOLUMNS (
OrderLines,
"UniqueCategories", RELATED ( Products[Product Category] )
)
)
)
RETURN
COUNTX (
FILTER (
SUMMARIZE (
Orderlines,
Orderlines[OrderNo],
"_1", DISTINCTCOUNT ( Products[Product Category] )
),
[_1] = _cnt && _cnt > 1
),
[OrderNo]
)
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 | |
96 | |
76 | |
63 | |
55 |
User | Count |
---|---|
142 | |
107 | |
89 | |
84 | |
65 |