Hello,
I have a typical facttable and a dimension table. My facttable is a order table where I track all orders and from which shelf something was taken:
| Shelfno | | OrderID |
| 1234 | | 111111 |
| 1235 | | 222222 |
| 1236 | | 333333 |
| 1237 | | 444444 |
| 1238 | | 555555 |
| 1239 | | 666666 |
| 1240 | | 777777 |
| 1241 | | 888888 |
| 1242 | | 999999 |
| 1243 | | 1111110 |
| 1244 | | 1222221 |
My dimension table consist of the details about which material is in the shelf:
| Shelfno | | Material |
| 9999 | | |
| 1240 | | mmm |
| 1244 | | nnn |
| 8786 | | |
| 1236 | | ggg |
| 1239 | | ddd |
| 5555 | | |
| 1234 | | dhd |
| 3333 | | |
| 1243 | | jdj |
| 5563 | | |
Both tables are connected via the shelf no. and bidrectional filtering is turned on.
I need the average distinctcount of OrderID of each Shelfno. And also disregard the shelfs with blank Materials.
I tried something with Averagex, Addcolumns and Summarize but the problem is that both are in different tables, and I also don't no how to filter out the blank Materials from the counts.
Thank you very much in advance.
Best.
