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.
Hi all,
I had last year and YTD percentage cards come back the same percentage. I then realized based on my data that I needed additional filters. I updated the Last year dax, but cannot get the DAX to work with filters and previous year.
Solved! Go to Solution.
Hi @dbrandone ,
First, please make sure that there is one relationship has been created between Date Table [Date] and vTransplantRate[PreservationOn]. Then please update the formula of measures [YTD Transplant Rate] and [Last Year Transplant Rate] as below separately:
YTD Transplant Rate =
VAR vNumerator =
CALCULATE (
vTransplantRate[TransplantRate Count],
vTransplantRate[TissueOutcome] = "Transplant",
DATESYTD ( 'Date Table'[Date] )
)
VAR vDenominator =
CALCULATE (
vTransplantRate[TransplantRate Count],
DATESYTD ( 'Date Table'[Date] )
)
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Last Year Transplant Rate =
VAR Numerator =
CALCULATE (
COUNT ( vTransplantRate[TissueOutcome] ),
vTransplantRate[TissueOutcome] = "Transplant",
DATEADD ( DATESYTD ( 'Date Table'[Date] ), -1, YEAR )
)
VAR Denominator =
CALCULATE (
COUNT ( vTransplantRate[RecoveryIntent] ),
vTransplantRate[RecoveryIntent] = "Transplant",
IF (
NOT ( ISBLANK ( vTransplantRate[TissueOutcome] ) ),
vTransplantRate[TissueOutcome]
),
DATEADD ( DATESYTD ( 'Date Table'[Date] ), -1, YEAR )
)
VAR Result =
DIVIDE ( Numerator, Denominator )
RETURN
Result
If the above ones can't return the correct result, please provide the related calculation logic and more details for your requirement. Thank you.
Best Regards
Hi @dbrandone ,
First, please make sure that there is one relationship has been created between Date Table [Date] and vTransplantRate[PreservationOn]. Then please update the formula of measures [YTD Transplant Rate] and [Last Year Transplant Rate] as below separately:
YTD Transplant Rate =
VAR vNumerator =
CALCULATE (
vTransplantRate[TransplantRate Count],
vTransplantRate[TissueOutcome] = "Transplant",
DATESYTD ( 'Date Table'[Date] )
)
VAR vDenominator =
CALCULATE (
vTransplantRate[TransplantRate Count],
DATESYTD ( 'Date Table'[Date] )
)
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Last Year Transplant Rate =
VAR Numerator =
CALCULATE (
COUNT ( vTransplantRate[TissueOutcome] ),
vTransplantRate[TissueOutcome] = "Transplant",
DATEADD ( DATESYTD ( 'Date Table'[Date] ), -1, YEAR )
)
VAR Denominator =
CALCULATE (
COUNT ( vTransplantRate[RecoveryIntent] ),
vTransplantRate[RecoveryIntent] = "Transplant",
IF (
NOT ( ISBLANK ( vTransplantRate[TissueOutcome] ) ),
vTransplantRate[TissueOutcome]
),
DATEADD ( DATESYTD ( 'Date Table'[Date] ), -1, YEAR )
)
VAR Result =
DIVIDE ( Numerator, Denominator )
RETURN
Result
If the above ones can't return the correct result, please provide the related calculation logic and more details for your requirement. Thank you.
Best Regards
@dbrandone , Try this change and check
Last Year Transplant Rate =
VAR LastYear =
PREVIOUSYEAR('Date Table'[Date], "12/31")
VAR Numerator =
CALCULATE(COUNT(vTransplantRate[TissueOutcome]), LastYear) vTransplantRate[TissueOutcome] = "Transplant")
VAR Denominator =
CALCULATE(COUNT(vTransplantRate[RecoveryIntent]), LastYear,
vTransplantRate[RecoveryIntent] = "Transplant", IF(NOT(ISBLANK(vTransplantRate[TissueOutcome])), vTransplantRate[TissueOutcome]))
VAR Result =
DIVIDE(Numerator, Denominator)
Return
Result
@amitchandak I get a "Couldn't load the data for this visual" error.
Error Message:
MdxScript(Model) (146, 61) Calculation error in measure 'iTx Measures'[vTR_Last Year Transplant Rate]: Cannot convert value 'Transplant' of type Text to type True/False.
Would this be a time where we would need to designate "transplant" as a value of 1, else 0 and then run a count? If so, how would I go about implementing that into the DAX?
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |