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.
I am trying to create a measure to calculate the percentage of the most recent month's positive feedbacks from my dataset and to get that I'm using the below measure.
Pre Month Survey positive =
VAR Prev_month =
MAX ( Sheet1[record date].[Month] )
VAR Current_year =
MAX ( Sheet1[record date].[Year] )
VAR pre_mont_top2 =
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( Sheet1[ID] ),
FILTER (
Sheet1,
Sheet1[feedback ] = "positive"
&& VALUE ( Sheet1[record date].[Month] ) = Prev_month
&& VALUE ( Sheet1[record date].[Year] ) = Current_year
)
),
CALCULATE (
DISTINCTCOUNT ( Sheet1[ID] ),
FILTER (
Sheet1,
VALUE ( Sheet1[record date].[Month] ) = Prev_month
&& VALUE ( Sheet1[record date].[Year] ) = Current_year
)
)
)
RETURN
pre_mont_top2
I'm not understanding why it's returning the "DAX Comparison Operations do not support comparing values of type number with values of type text " error
Solved! Go to Solution.
Hi, @Sharma0815
According to your description, I can understand clearly what you want to get, you want to get the percentage of the most recent month's positive feedbacks, right? I also created some data and used DAX to achieve this, you can try my steps:
This is my test data based on your picture:
datediff =
DATEDIFF([record date],TODAY(),DAY)
Percent =
var _leastdatadiff=
CALCULATE(MIN('Table'[datediff]),FILTER(ALL('Table'),[feedback]="Positive"))
var _mostrecentmonth=
MONTH(CALCULATE(MAX('Table'[record date]),FILTER(ALL('Table'),[datediff]=_leastdatadiff&&[feedback]="positive")))
var _mostrecentyear=
YEAR(CALCULATE(MAX('Table'[record date]),FILTER(ALL('Table'),[datediff]=_leastdatadiff&&[feedback]="positive")))
var _countofpositive=
COUNTX(FILTER(ALL('Table'),MONTH([record date])=_mostrecentmonth&&YEAR([record date])=_mostrecentyear&&[feedback]="positive"),[ID])
var _countofall=
COUNTX(FILTER(ALL('Table'),MONTH([record date])=_mostrecentmonth&&YEAR([record date])=_mostrecentyear),[ID])
return
DIVIDE(_countofpositive,_countofall)
And I think the result can be what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result so that we can help you in advance.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Sharma0815
According to your description, I can understand clearly what you want to get, you want to get the percentage of the most recent month's positive feedbacks, right? I also created some data and used DAX to achieve this, you can try my steps:
This is my test data based on your picture:
datediff =
DATEDIFF([record date],TODAY(),DAY)
Percent =
var _leastdatadiff=
CALCULATE(MIN('Table'[datediff]),FILTER(ALL('Table'),[feedback]="Positive"))
var _mostrecentmonth=
MONTH(CALCULATE(MAX('Table'[record date]),FILTER(ALL('Table'),[datediff]=_leastdatadiff&&[feedback]="positive")))
var _mostrecentyear=
YEAR(CALCULATE(MAX('Table'[record date]),FILTER(ALL('Table'),[datediff]=_leastdatadiff&&[feedback]="positive")))
var _countofpositive=
COUNTX(FILTER(ALL('Table'),MONTH([record date])=_mostrecentmonth&&YEAR([record date])=_mostrecentyear&&[feedback]="positive"),[ID])
var _countofall=
COUNTX(FILTER(ALL('Table'),MONTH([record date])=_mostrecentmonth&&YEAR([record date])=_mostrecentyear),[ID])
return
DIVIDE(_countofpositive,_countofall)
And I think the result can be what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result so that we can help you in advance.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @Sharma0815 ,
I do not recommend using the DateType specific extensions like so [datevalue].Month as I consider them not being consistent as this
[2021-02-21].Month returns a "February" whereas this
[2021-02-21].Year returns 2021 and the datatype is "whole number", something we might expect.
The conditional expression VALUE( [datecolumn].Month = Prev_Month in your statement is responsible for the error you are facing. The DAX engine check is as follows (simplified):
Hmm, I know VALUE( ... ) will return a number (the left hand side of the conditional expression) but the right hand side is a text, for this I will return an error as I do not execute implicit type converson (basically this is a good thing).
Instead I'm recommending using the DAX functions MONTH( '...'[datevalue] ) and YEAR(...) as both return the same data type.
Hopefully, this explains why you are facing the error message.
Use YEAR(...) and MONTH(...), instead of '...'[...].Month and '...'[...].Year
Regards,
Tom
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |