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,
I need help creating a custom measure that will return the % of [Claim ID] that have a [Days Between] value equal to or less than the reference values in another table. This percentage would need to calculate the percentage based on only that [Company] number of distinct [Claim ID]. I need the measure to by dynamic so that it calculates based on a Slicer's selection for that [Company]
Here is an example data set with the Claim Data:
Here is an example data set with the reference values:
Given this example data, I would expect the measure to return a value of 50% when USA is selected from the slicer and 67% when Korea is selected.
- USA should = 50% since 3 out of 6 distinct [Claim ID] had a [Days Between] value equal to or less than 30
- Korea should = 67% since 2 out of 3 distinct [Claim ID] had a [Days Between] value equal to or less than 20
Can you please assist me in creating this type of measure?
Thank you,
Andrew
Solved! Go to Solution.
Hi @AW1976NOVA
Try this...
% Claims Better Than Goal =
VAR ClaimSummary =
ADDCOLUMNS(
Claims,
"DaysBetween",
DATEDIFF(
Claims[StartDate],
Claims[EndDate],
DAY
),
"Goal",
RELATED(Companies[Goal])
)
VAR ClaimCount = COUNTROWS(ClaimSummary)
VAR ClaimsBeatingGoalCount =
COUNTROWS(
FILTER(
ClaimSummary,
[DaysBetween] <= [Goal]
)
)
RETURN
DIVIDE(
ClaimsBeatingGoalCount,
ClaimCount,
BLANK()
)
Hope this helps!
Hi, @AW1976NOVA
Create a calculated col in datatable
@AW1976NOVA if you change the data type from a duration to whole number you should be ok. Worked with the pbix I attached.
Hi, @AW1976NOVA
Create a calculated col in datatable
Hi @AW1976NOVA
Try this...
% Claims Better Than Goal =
VAR ClaimSummary =
ADDCOLUMNS(
Claims,
"DaysBetween",
DATEDIFF(
Claims[StartDate],
Claims[EndDate],
DAY
),
"Goal",
RELATED(Companies[Goal])
)
VAR ClaimCount = COUNTROWS(ClaimSummary)
VAR ClaimsBeatingGoalCount =
COUNTROWS(
FILTER(
ClaimSummary,
[DaysBetween] <= [Goal]
)
)
RETURN
DIVIDE(
ClaimsBeatingGoalCount,
ClaimCount,
BLANK()
)
Hope this helps!
I tried this but the
RELATED(Companies[Goal]) portion of the DAX gave me trouble. My reference table has a numerical value set while the DATEDIFF is returning a interval of days. So, the DAX isn't finding a similar data type.
How can I resolve this issue?
@AW1976NOVA DATEDIFF returns the count of interval boundaries crossed between two dates: a numeric result. It clearly works...
Could you share your pbix to try to figure this out?
I verified in Power Query that [Adjudicated Days] is set as a numerical value, table name = '
Here is a screen grab of the DAX. The RELATED ( Adjudicated_PG_30[Adjudicated Days] ) returns an error of "Parameter is not correct type" and it then cannot find the column name "Adjudicated Days"
Oh no. I was just showing you that I confirmed in Power Query that
@AW1976NOVA if you change the data type from a duration to whole number you should be ok. Worked with the pbix I attached.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |