Forum Discussion
Count from Fact table and Week view
Trying to present in a visual the following information.
My Fact has below data
| Doc ID | Staus ID | Status Desc | Site | Date | Customer |
| 1000 | 1 | In Quote | A | 1/1/2021 | 2000 |
| 1020 | 2 | In Request | A | 1/4/2021 | 2001 |
| 1030 | 3 | In Order | A | 1/4/2021 | 2010 |
| 1040 | 4 | In Ship | A | 1/4/2021 | 2010 |
| 1050 | 3 | In Order | B | 1/10/2021 | 2015 |
| 1060 | 3 | In Request | B | 1/10/2021 | 2020 |
| 1070 | 2 | In Request | C | 1/10/2021 | 2025 |
| 1080 | 4 | In Ship | C | 1/10/2021 | 2025 |
| 1021 | 2 | In Request | A | 1/4/2021 | 2001 |
| 1022 | 3 | In Order | A | 01/5/2021 | 2001 |
| 1023 | 3 | In Order | A | 01/6/2021 | 2001 |
Trying to get a count of the status by the Site and present it as below and allowing user to drill down to the facts based on the 2 slicers.
If JAN month is selected, want to present a week view like below and need to drill in to the details of 2 doc's on the day selected.
| Status | 4-Jan | 5-Jan | 6-Jan | 7-Jan | 8-Jan |
| In Request | 2 Docs | ||||
| In Order | 1 Doc | 1 Doc | 1 Doc | ||
| In Ship | 1 Doc | ||||
| In Request | 2 Docs |
My Questions
1. I tried using a summary table, but for count how to group for 2 columns. Is there a better way to do this than the summarize?
2. Is using a measure to calculate count, the better approach from performance point
3. How to restrict 1 week view in the visual or should te slicer be restricted to be chosen for 1 week view
Please advice.
Attaching the pbix here.
https://drive.google.com/file/d/14tYLgZzCYKoJ3nT5G5-CX7k-Pf926n-N/view?usp=sharing
Hi Anonymous ,
1&2.You can try to create the following measure instead of the calculated table.
Measure = CALCULATE( COUNT(Trans[Doc ID]), ALLEXCEPT(Trans,Trans[Site],Trans[Status Desc] ))3. Based on your description, I'm not sure how you want to limit the number of weeks.
You can create a measure and put it into visual level filter.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Anonymous ,
1&2.You can try to create the following measure instead of the calculated table.
Measure = CALCULATE( COUNT(Trans[Doc ID]), ALLEXCEPT(Trans,Trans[Site],Trans[Status Desc] ))3. Based on your description, I'm not sure how you want to limit the number of weeks.
You can create a measure and put it into visual level filter.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.