Forum Discussion
Remove duplicate ID's between 2 dynamic dates using a slider
I have been trying to figure this out for a few days and cannot figure out a solution for my report I am currently building.
The data I'm working with shows placed assignments that has information such as the start and end dates, the candidate ID, the assignment ID, etc. What I am trying to do is show a table with all this information, easy, and a slider that has 0 through 15 to indicate how many weeks out I want to display this information based on the end date, again easy.
The issue I'm running into is that I need to remove all rows for any candidate id that has 2 assignments within the dynamic weeks out range from the slider.
I can accomplish this in the data table through Dax for a set date, but since I want this to be controlled by the slider for the weeks out it needs to be dynamic, which is where I'm getting lost.
I hope this makes sense and I can provide more information as needed. Any suggestions on what I can do would be greatly appreciated! Thank you!
Hi Jkrupski,
Thank you for reaching out to Microsoft Fabric Community.
Thank you lbendlin for the prompt response
First create a calculated table like below(WeekTable with column name WeeksOut), then create a measure that specified as below which is "AssignmentDetails":
WeeksTable = GENERATESERIES(1, 15, 1)
AssignmentDetails =
VAR SelectedWeeks = SELECTEDVALUE(WeeksTable[WeeksOut], 15) -- Defaults to 15 if no slicer selection
VAR CandidateCount = CALCULATE(
DISTINCTCOUNT('Assignment'[Assignment ID]),
ALLEXCEPT('Assignment', 'Assignment'[Candidate ID])
)
RETURN
IF(
CandidateCount = 1 && MAX('Assignment'[Weeks Out]) <= SelectedWeeks,
1,
0
)
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
6 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hello lbendlin here is some data and further information.
I have a table:
Candidate ID Assignment ID Weeks Out 1 111 1 1 112 12 2 113 3 3 114 4 What I want to do is have a slicer that dictates how many weeks out the data will show, and when a candidate has 2 assigments I want to remove both from being displayed.
The slicer will go up to 15, so weeks 1 through 11 would display assignments 111, 113, 114. When the slicer goes to 12 and beyond, then only assignments 113 and 114 would be displayed.
Let me know if more info is needed, thank you!
Hi Jkrupski,
Thank you for reaching out to Microsoft Fabric Community.
Thank you lbendlin for the prompt response
First create a calculated table like below(WeekTable with column name WeeksOut), then create a measure that specified as below which is "AssignmentDetails":
WeeksTable = GENERATESERIES(1, 15, 1)
AssignmentDetails =
VAR SelectedWeeks = SELECTEDVALUE(WeeksTable[WeeksOut], 15) -- Defaults to 15 if no slicer selection
VAR CandidateCount = CALCULATE(
DISTINCTCOUNT('Assignment'[Assignment ID]),
ALLEXCEPT('Assignment', 'Assignment'[Candidate ID])
)
RETURN
IF(
CandidateCount = 1 && MAX('Assignment'[Weeks Out]) <= SelectedWeeks,
1,
0
)
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi Jkrupski,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara ReddyHi @Jkrupski,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara ReddyHi @Jkrupski,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara Reddy
