Categorising and Counting Within a Dynamic Time Period

[elinevans1]

Hello, 

I have a table similar to the following:

Person ID	Form Date	Form ID	Form Name
123	01/01/2024	101	Missing Episode
345	02/02/2024	101	Missing Episode
567	03/03/2024	123	Found Return

I also have a seperate date table. 

Within a dynamic date range (determined by the user using a slicer) I would like to have the % of missing episodes that are considered a repeat missing episode.

So it would be something like: 

Calculate per child how many missing episodes they had within the selected time period, and for children where that figure is more than 1, count the total of missing episodes

Divided by

Total number of missing episodes

I can't seem to get my head around how to calculate the first bit of the equation, if it's possible at all?

Thanks for any help in advance

[v-nmadadi-msft]

Hi @elinevans1 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @elinevans1 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @elinevans1,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft · Accepted solution]

Hi @elinevans1,
Thanks for reaching out to the Microsoft fabric community forum.

I've replicated the DAX measures on my end and generated the desired output. I've uploaded the .pbix file please take a look and let me know if it aligns with your requirements.

Additionally, the third DAX measure returns a division result, which does not represent the percentage value. To display it correctly as a percentage, go to the visual's formatting options, navigate to Data format, and change the format from General to Percentage. This will ensure the value is presented with the % symbol for better readability.

 


If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[bhanu_gautam]

@elinevans1 Create a measure to count the total number of missing episodes:

DAX
TotalMissingEpisodes =
CALCULATE(
COUNTROWS('Table'),
'Table'[Form Name] = "Missing Episode"
)

Create a measure to count the number of repeat missing episodes:

DAX
RepeatMissingEpisodes =
CALCULATE(
COUNTROWS('Table'),
'Table'[Form Name] = "Missing Episode",
FILTER(
'Table',
CALCULATE(
COUNTROWS('Table'),
'Table'[Person ID] = EARLIER('Table'[Person ID]),
'Table'[Form Name] = "Missing Episode"
) > 1
)
)

Create a measure to calculate the percentage of repeat missing episodes:

DAX
PercentageRepeatMissingEpisodes =
DIVIDE(
[RepeatMissingEpisodes],
[TotalMissingEpisodes],
0
)

Ensure your date slicer is connected to the date table and the main table.

Add a card visual to display the PercentageRepeatMissingEpisodes measure.
Add a slicer visual to allow users to select the date range.