powerbidaxpowerbi-desktopdaxstudio

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

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, initial table with status, a semgment,a table with what exclude etc, results. 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)

enter image description here

link to chart-table

Solution

  • To filter your table visual by Close Date but keep the rows with blank values try this:

    1. Create a Date Table
    2. User Date Table with slicer
    3. Create the following measure
    4. Add Measure to your table visual's filters as Show items when value is 1

    Date Table:

    _Date Table = CALENDAR(MIN('POA 2024'[Close Date]), MAX('POA 2024'[Close Date]))

    Note: Do not create relationship between _Date Table and POA 2024

    Use the _Date Table[Date] column in your slicer.

    Measure:

    ShowRows = 
var slicerVals = ALLSELECTED('_Date Table'[Date]) //Get all selected values from slicer.

var showRow = IF(
                ISEMPTY(
                    FILTER(slicerVals,
                        VAR closedDate = MAXX('POA 2024', 'POA 2024'[Close Date]) //Retrieves Close Date from Table and stores it into a variable to reuse.
                        RETURN
                        closedDate IN slicerVals //Checks if Closed Date is in Slicer Selection
                        || closedDate = BLANK()  //Checks if Closed Date is blank, because we want to show these rows
                    )
                     )
                     ,0,1) //Returns 0 if the the criteria is not met, or 1 if the criteria is met. 1 = Show, 0 = Hide

return showRow

    Edit: Adding filtering for visual below:

    The measure requires all fields in the visual to work properly.

    So select the visual and put your Closed Date column under the main group.

    Donut Chart