Forum Discussion

lg_analyst's avatar
lg_analyst
Icon for Helper I rankHelper I
5 years ago

Single row Filtering affecting multiple rows

Hi all,   I'm struggling satysfying a business request on a dashboard here. I need to display some information about shipments. Each Order ID can be shipped in a single Shipment ID or splitted in m...
Need Help
Tips and Tricks
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

lg_analyst,

 

Try this solution. It requires two disconnected date tables (no relationships), with a slicer based on each.

 

1. Data model:

 

 

2. Measure:

 

Display Row = 
VAR vStartDate =
    SELECTEDVALUE ( StartDate[Date] )
VAR vEndDate =
    SELECTEDVALUE ( EndDate[Date] )
VAR vRowCountStartDate =
    CALCULATE (
        COUNTROWS ( Shipment ),
        ALLEXCEPT ( Shipment, Shipment[Order ID] ),
        Shipment[Start Date] = vStartDate
    )
VAR vRowCountEndDate =
    CALCULATE (
        COUNTROWS ( Shipment ),
        ALLEXCEPT ( Shipment, Shipment[Order ID] ),
        Shipment[End Date] = vEndDate
    )
VAR vResult =
    SWITCH (
        TRUE (),
        // if Start Date and End Date are not specified
        ISBLANK ( vStartDate ) && ISBLANK ( vEndDate ), 1,
        // if an order has at least one row corresponding to the selected date
        vRowCountStartDate > 0
            || vRowCountEndDate > 0, 1
    )
RETURN
    vResult

 

3. Add measure [Display Row] to visual filter (set to "is 1"):

 

 

4. Result:

 

-----------------------------------------------------------

-----------------------------------------------------------