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.
Solved! Go to Solution.
@priya_rajendran
Please use the correct column names inside SUMMARIZE
Open_reqs =
SUMX (
SUMMARIZE ( calendar_dim, calendar_dim[Year], calendar_dim[Month] ),
CALCULATE (
VAR start_ =
STARTOFMONTH ( calendar_dim[cal_dt] )
VAR end_ =
ENDOFMONTH ( calendar_dim[cal_dt] )
VAR thisday =
TODAY ()
VAR openreq =
IF (
start_
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 30,
CALCULATE (
COUNTROWS ( Expected_joiners ),
ALLEXCEPT ( Expected_joiners, 'CC Details'[Tech Org 1], 'CC Details'[IDC CC] ),
Expected_joiners[Req Approved On] <= end_,
Expected_joiners[Candidate Joining Date] = BLANK ()
)
)
RETURN
openreq
)
)
@priya_rajendran
Please use the correct column names inside SUMMARIZE
Open_reqs =
SUMX (
SUMMARIZE ( calendar_dim, calendar_dim[Year], calendar_dim[Month] ),
CALCULATE (
VAR start_ =
STARTOFMONTH ( calendar_dim[cal_dt] )
VAR end_ =
ENDOFMONTH ( calendar_dim[cal_dt] )
VAR thisday =
TODAY ()
VAR openreq =
IF (
start_
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 30,
CALCULATE (
COUNTROWS ( Expected_joiners ),
ALLEXCEPT ( Expected_joiners, 'CC Details'[Tech Org 1], 'CC Details'[IDC CC] ),
Expected_joiners[Req Approved On] <= end_,
Expected_joiners[Candidate Joining Date] = BLANK ()
)
)
RETURN
openreq
)
)
Hi @tamerj1 , Thanks for your response! Your Dax returned the total and it works. My only concern is that I dont want the total of the values here. I would still want the count of rows in the total too as my earlier dax did. would it be possbile to get the same number 1795 instead of the sum 12565 ?
@tamerj1 , Slicing by month alone also returns the same result.
Hi @priya_rajendran
Now the slicing by has changed from Year/Month hierarchy to month only.
Please use
Open_reqs =
SUMX (
VALUES ( calendar_dim[Month] ),
CALCULATE (
VAR start_ =
STARTOFMONTH ( calendar_dim[cal_dt] )
VAR end_ =
ENDOFMONTH ( calendar_dim[cal_dt] )
VAR thisday =
TODAY ()
VAR openreq =
IF (
start_
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 30,
CALCULATE (
COUNTROWS ( Expected_joiners ),
ALLEXCEPT ( Expected_joiners, 'CC Details'[Tech Org 1], 'CC Details'[IDC CC] ),
Expected_joiners[Req Approved On] <= end_,
Expected_joiners[Candidate Joining Date] = BLANK ()
)
)
RETURN
openreq
)
)
Also please copy paste the code that your are using in your file so I can check it
Hi, The slicing by month or year & month, both returns the same value
Hi @priya_rajendran
Please try
Open_reqs =
VAR start_ =
MIN ( calendar_dim[cal_dt] )
VAR end_ =
MAX ( calendar_dim[cal_dt] )
VAR thisday =
TODAY ()
VAR openreq =
IF (
start_
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 30,
CALCULATE (
COUNTROWS ( Expected_joiners ),
ALLEXCEPT ( Expected_joiners, 'CC Details'[Tech Org 1], 'CC Details'[IDC CC] ),
Expected_joiners[Req Approved On] <= end_,
Expected_joiners[Candidate Joining Date] = BLANK ()
)
)
RETURN
openreq
Hi @tamerj1 This does not give the totals value. This works same as my 1st dax. Your summarize dax actually works perfectly fine for sum, but only for the measures where i am looking for the count aggregate in total, I am stuck.
Appreciate your help!
Can we connect via zoom or teams? If so please share a meeting link in a private message. Thank you
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 |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |