cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## Measure: Calculate % of results that equal criteria within another table

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

3 ACCEPTED SOLUTIONS
Community Champion

Try this...

``````% Claims Better Than Goal =
VAR ClaimSummary =
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!

Super User

Hi, @AW1976NOVA

Create a calculated col in datatable

Goal = LOOKUPVALUE(RefrenceTable[Goal],RefrenceTable[Company],'DataTable'[Company])

Then use this formula
Measure = VAR _DistinctCountClaimID = CALCULATE(DISTINCTCOUNT('DataTable'[Claim ID]),ALLEXCEPT('DataTable','DataTable'[Company]))
VAR _days = SELECTEDVALUE('DataTable'[Goal])
VAR _dayscount = CALCULATE(COUNT('DataTable'[Company]), 'DataTable'[Days Between] <= _days)
RETURN
_dayscount/_DistinctCountClaimID

If you have any question, please let me know.
If this comment helps you, please LIKE this comment/Kudos

Proud to be a Super User!

Community Champion

@AW1976NOVA if you change the data type from a duration to whole number you should be ok.  Worked with the pbix I attached.

8 REPLIES 8
Super User

Hi, @AW1976NOVA

Create a calculated col in datatable

Goal = LOOKUPVALUE(RefrenceTable[Goal],RefrenceTable[Company],'DataTable'[Company])

Then use this formula
Measure = VAR _DistinctCountClaimID = CALCULATE(DISTINCTCOUNT('DataTable'[Claim ID]),ALLEXCEPT('DataTable','DataTable'[Company]))
VAR _days = SELECTEDVALUE('DataTable'[Goal])
VAR _dayscount = CALCULATE(COUNT('DataTable'[Company]), 'DataTable'[Days Between] <= _days)
RETURN
_dayscount/_DistinctCountClaimID

If you have any question, please let me know.
If this comment helps you, please LIKE this comment/Kudos

Proud to be a Super User!

Community Champion

Try this...

``````% Claims Better Than Goal =
VAR ClaimSummary =
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!

Post Patron

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?

Community Champion

@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?

Post Patron

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"

Community Champion

@AW1976NOVA you're trying to solve this in Power Query???  😯

Post Patron

Oh no.  I was just showing you that I confirmed in Power Query that

Community Champion

@AW1976NOVA if you change the data type from a duration to whole number you should be ok.  Worked with the pbix I attached.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors