Forum Discussion
Power BI DAX for sub category
I am having a dataset at the below
Month | Week Number | Userid | Ratings |
Dec-21 | W52 | 94 | Disagree |
Dec-21 | W53 | 95 | Strongly Agree |
Dec-21 | W54 | 96 | Neutral |
Dec-21 | W55 | 97 | Agree |
Dec-21 | W56 | 98 | Neutral |
Dec-21 | W57 | 99 | Agree |
Jan-22 | W01 | 100 | Strongly Agree |
Jan-22 | W01 | 101 | Strongly Agree |
Jan-22 | W01 | 102 | Neutral |
Jan-22 | W01 | 103 | Strongly Agree |
Jan-22 | W01 | 104 | Agree |
Jan-22 | W01 | 105 | Agree |
Jan-22 | W01 | 106 | Strongly Disagree |
Jan-22 | W01 | 107 | Neutral |
Jan-22 | W02 | 108 | Strongly Agree |
Jan-22 | W02 | 109 | Strongly Agree |
Jan-22 | W02 | 110 | Strongly Agree |
Jan-22 | W02 | 111 | Strongly Agree |
Jan-22 | W02 | 112 | Neutral |
Jan-22 | W02 | 113 | Strongly Disagree |
Jan-22 | W02 | 114 | Disagree |
Jan-22 | W02 | 115 | Agree |
Jan-22 | W02 | 116 | Strongly Agree |
Jan-22 | W02 | 117 | Neutral |
Jan-22 | W03 | 118 | Strongly Agree |
Jan-22 | W03 | 119 | Strongly Agree |
Jan-22 | W03 | 120 | Agree |
Jan-22 | W03 | 121 | Agree |
I have a Slicer in PBI. When W03 is selected, my ideal output is at the below
Values | Count |
Strongly Agree | 2 |
Agree | 2 |
Neutral | 0 |
Disagree | 0 |
Strongly Disagree | 0 |
Jan-23 MTD based on W03 selection
MTD - Jan | Count |
Strongly Agree | 10 |
Agree | 5 |
Neutral | 4 |
Disagree | 1 |
Strongly Disagree | 2 |
I came up with a DAX formula at the below
CSAT_Delivery_Count_MTD =
var SelectedDate=CALCULATE(MAX(FACT_Responses[CAL_WEEK_END_DT]),FACT_Responses[Year Week]=SELECTEDVALUE(FACT_Responses[Year Week]))
VAR DATEFILTER= DATESBETWEEN(
FACT_Responses[CAL_WEEK_END_DT],
calculate(MIN(FACT_Responses[CAL_WEEK_END_DT]),all(FACT_Responses),FACT_Responses[Month]=SELECTEDVALUE(FACT_Responses[Month]),FACT_Responses[Year]=SELECTEDVALUE(FACT_Responses[Year])),
SelectedDate)
return
CALCULATE( [CSAT_Delivery_Count],ALL(FACT_Responses),DATEFILTER,VALUES(FACT_Responses[CSAT_Delivery]))
However it seems that I could not get the MTD if the selected weeks has count by each category = 0
this was my result which is wrong
MTD - Jan | Count |
Strongly Agree | 10 |
Agree | 5 |
Neutral | 0 |
Disagree | 0 |
Strongly Disagree | 0 |
Can advise how to resolve this?
2 Replies
Hi,
For MTD do this:
1. create a calendar table and relationship to you fact table:2. create MTD measure like this:
Rating_MTD = CALCULATE([Measure 43],DATESMTD('Calendar'[Date]))
(The measure 43 is just COUNT of the UserID table)
3. End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/i am sorry but it is not working for me.
It is not returning any values
CALCULATE([CSAT_Delivery_Count],DATESMTD(DIM_Date[CAL_WEEK_END_DT]))
