how to segment for a subcategory of the data and not the others when they are in the same object

[Anonymous]

Hello, please I need help with this. I have a table with tasks and their status, completed tasks have an end date.
I need a page to have a segmenter that takes the closing date (or some calculated form) and this filters the ones completed by this date but
leaves the others that do not have a date alone. Is not with the interactions because the data is in the same object (table or graph):
Example in the picture, a table with status, a semgment, what exclude etc.
Now if I put that, when I move the date filter from the minimum date it excludes those that do not have a date.
I already tried to generate a column that brings me the maximum closing date to put it to those that do not have it and so
I am always using this column as a segmenter, but this maximum remains static and not dynamic

ff = VARMaxAllDate = CALCULATE(Max('POA 2024'[Close Date]), ALL('POA 2024')) VAR HasClosingDate = NOT(ISBLANK('POA 2024'[Close Date])) RETURN IF(HasClosingDate, 'POA 2024'[Close Date], MaxAllDate)

    or

ff = VARMaxAllDate = CALCULATE(Max('POA 2024'[Close Date]), ALLselected('POA 2024'[Close Date])) VAR HasClosingDate = NOT(ISBLANK('POA 2024'[Close Date])) RETURN IF(HasClosingDate, 'POA 2024'[Close Date], MaxAllDate)   

Task	status	Date close
1	Finished	1/07/2024
2	In planning
3	scheduled
4	scheduled
5	Finished	8/07/2024
6	empty
7	close to finish
8	Finished	4/06/2022
9	Finished	1/02/2024

 how the filter act

Task	status	measure 1	measure 2	EXCLUDED BY SEGMENT DAY CLOSE    BEEWTWEN  01/01/2023   01/04/2024 FOR EXAMPLE	DATE CLOSE
1	Finished			NO	1/03/2024
2	In planning			NO
3	scheduled			NO
4	scheduled			NO
5	Finished			YES	8/07/2024
6	empty			NO
7	close to finish		NO
8	Finished			YES	4/06/2022
9	Finished			NO	1/02/2024

 final table

Task	status	measure 1	measure 2
1	Finished
2	In planning
3	scheduled
4	scheduled
6	empty
7	close to finish
9	Finished

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result very clearly.  Also, clearly state the selection that you would have in the slicer.

[Anonymous]

Hi @Anonymous ,

I made simple samples and you can check the results below:

DimCloseDate = CALENDAR(MIN('Table'[Date close]),MAX('Table'[Date close])) 

Measure = var _min = MIN('DimCloseDate'[Date])
var _max = MAX('DimCloseDate'[Date])
var _Close = MAX('Table'[Date close])
RETURN IF(_Close>=_min&&_Close<=_max&&MAX('Table'[status])="Finished",1,0)

Please feel free to correct me and provide more information if I have misunderstood you!

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, thanks for answering. As I see there you would filter only finished ones, that is the problem, I need the unfinished ones to be maintained and the segmenter with some date (close date or some calculated) to filter on the finished ones. As seen in the example, the unfinished ones remain and the finished ones are filtered by date. To test, it is good to run the smaller date date segmenter because there it excludes those that do not have a closing date. thanks

[Anonymous]

Hi @Anonymous ,

I still don't seem to get your point, please show your expected results.

Best regards,
Community Support Team_ Scott Chang

[Anonymous]

Hi, how are you? sorry to bother you. I see that you are close to the solution and you know a lot, could you please help me with this missing part?

[Anonymous]

Hi @Anonymous ,

Is this the result you wanted?

Measure = var _min = MIN('DimCloseDate'[Date])
var _max = MAX('DimCloseDate'[Date])
var _Close = MAX('Table'[Date close])
RETURN IF(_Close>=_min&&_Close<=_max&&MAX('Table'[status])="Finished"||MAX('Table'[status])<>"Finished",1,0)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, how are you. I think this is, but I don't know if I will have the same problem as another solution you gave me that worked in a table but in a chart count the filter did not work. On the other hand, how do I implement your solution, I am trying to do it but if I do not establish a relation between the created date and the close date it does nothing, but if I establish then the filter eliminates those that are different to finished because they do not have close date as such.

[Anonymous]

Hi @Anonymous ,

Please provide your .pbix sample file, as well as the expected results.

Best regards,
Community Support Team_ Scott Chang

[Anonymous]

Hi, its  me again. Thanks for all you help 

I leave you the pbix https://drive.google.com/file/d/1vboJHXe1HpXQ2lra0ynlsc6g7SsZBrzy/view?usp=drivesdk , it is in Spanish but I leave you the homologation of what you wrote with what is there.

DimCloseDate = CALENDAR(MIN('POA 2024'[Fecha de cierre]),MAX('POA 2024'[Fecha de cierre])) 
DimCloseDate = CALENDAR(MIN('Table'[Date close]),MAX('Table'[Date close])) 


Measure = var _min = MIN('DimCloseDate'[Date])
var _max = MAX('DimCloseDate'[Date])
var _Close = MAX('Table'[Date close])
RETURN IF(_Close>=_min&&_Close<=_max&&MAX('Table'[status])="Finished"||MAX('Table'[status])<>"Finished",1,0)

Measure = var _min = MIN('DimCloseDate'[Date])
var _max = MAX('DimCloseDate'[Date])
var _Close = MAX('POA 2024'[Fecha de cierre])
RETURN IF(_Close>=_min&&_Close<=_max&&MAX('POA 2024'[EstadoActividad])="Finalizada"||MAX('POA 2024'[EstadoActividad])<>"Finalizada",1,0)

In the file you find the original and the exercise . In the original you have all the statuses, in the exercise the idea is to be able to show all the statuses apart from finished “Finalizada” and to show the finished ones that are in the filter date. Currently as it is, the statuses other than finished are eliminated.

[Anonymous]

Hi, the results were in the picture, however now are in the post orginal in tables. thanks

[Anonymous]

I  guess if the mesure is like this its work but only for table with the activity, for a graph with status as general does not work

Measure = var _min = MIN('DimCloseDate'[Date])

var _max = MAX('DimCloseDate'[Date])

var _Close = MAX('Table'[Date close])

RETURN IF(_Close>=_min&&_Close<=_max || MAX('Table'[status])<>"Finished",1,0)

[amitchandak]

@Anonymous , try if this measure can help

Task Display =
VAR _max = MAXX(allselected('POA 2024'),'POA 2024'[Close Date])
VAR _istaskclosed = NOT ISBLANK(Max('POA 2024'[Close Date]))
VAR _closed = Max('POA 2024'[Close Date])
RETURN
IF(
_istaskclosed, IF( _closed <= _max, 1, 0 ),
1
)