Passing a filtered variable to an unfiltered table

[vonRobbo]

Hi all,

I'm attempting to use a single selection in a slicer as a variable in a DAX formula, but having issues....

Here are the formulas:

CriticalEvent measure:

CriticalEvent = "1.40.50.90"

Total Float Test Measure:

Total Float TEST = 
VAR CriticalEvent = [CriticalEvent]
RETURN
    Calculate(if(ABS(Min('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]))>Min('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]),Min('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]),ABS(Min('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]))),

(CONTAINSSTRING(
'(Schedule) FACT- Schedule Data'[CLPI Activities],CriticalEvent)
))

When I use the variable Critical Event , my formula behaves as intended. I put it in a table that has a series of [Activity IDs] and I only get the [Total Float] for the rows where the string "1.40.50.90" appears in the [CLPI Activities] column.

This is exactly what I want my Table to look like:

I don't want users to have to manually input the Activity ID into the CriticalEvent DAX formula, so I set up a slicer (sinlge selection) with [Activity ID] and updated Critical Event:

CriticalEvent = SELECTEDVALUE('(Schedule) FACT- Schedule Data'[Activity ID])

This works, but the Slicer is going to cut out the all other rows from my Table when I select "1.40.50.90".

This is obviously expected behaviour, but not what I need.

I only want that slicer to be used to provide the variable value for the DAX formula without affecting filtering the ActivityID in my table, so I used 'Edit Interactions' to stop the Table from looking at the Slicer. Now I have a [Total Float] for each and every ActivityID,

I believe the problem comes when referenced in a Measure in a Table that isn't connected to the Slicer, the CriticalEvent Measure is disregards the Slicer. When used in a Table that isn't looking at the Slicer, the SelectedValue of CriticalEvent is now all Activity IDs in the data rather than the single Activity ID selected in the Slicer.

TLDR, I 'm tring to get the CriticalPath Measure to respect the Slicer when it's in used in a Table that is not respecting the Slicer. I started reading about the different FILTER functions, but don't know which one is appropriate or where I would use it here.

Possible ideas (help requested):

- Can I convert the output of the CriticalEvent measure to a text string that doesn't change when I bring it into my first measure?

- Is there another way to make sure that the output of the CriticalEvent  measure respects the Slicer selection when I use it in a Measure in a Table that isn't affected by the Slicer?

-Maybe there is a completely different way passing the Activity ID from the Slicer to the Measure in the Table not looking at the Slicer.

I appreciate any help provided and I hope I've made my problem clear.

[johnt75 · Accepted solution]

Create a disconnected table to use in the slicer and the CriticalEvent measure like

Event for slicer = DISTINCT( '(Schedule) FACT- Schedule Data'[Activity ID] )

Critical Event = SELECTEDVALUE( 'Event for slicer'[Activity ID] )

Your original Total float measure should then work.

[vonRobbo]

This worked! I would have liked to achieve the result without adding another 500+ row table to my dataset, but this will do for now! Thanks so much.

[VahidDM]

@vonRobbo 
Try this:

To ensure your measure uses the slicer selection without affecting the visual filtering:

1. Adjust your CriticalEvent measure:

CriticalEvent =
CALCULATE(
    SELECTEDVALUE('(Schedule) FACT- Schedule Data'[Activity ID]),
    ALLSELECTED('(Schedule) FACT- Schedule Data'[Activity ID])
)

2. Adjust your Total Float TEST measure:

Total Float TEST =
VAR CriticalEvent = [CriticalEvent]
RETURN
    CALCULATE(
        IF(
            ABS(MIN('(Schedule) FACT- Schedule Data'[Float (Calendar Days)])) >
                MIN('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]),
            MIN('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]),
            ABS(MIN('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]))
        ),
        CONTAINSSTRING('(Schedule) FACT- Schedule Data'[CLPI Activities], CriticalEvent)
    )

ALLSELECTED ensures the slicer selection passes into the measure even if the visual interaction is turned off. The measure retrieves your slicer-selected Activity ID, independent from your visual's direct filtering context.

This allows your table visual to show all rows but still respects the single Activity ID selection made in your slicer.

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[vonRobbo]

@VahidDMthanks for taking the time to help me, I appreciate it. I copied your updates exactly (although it looked like the only change you made to Total Float TEST was to fix the layout, which I appreciate).

CriticalEvent = 
CALCULATE(
    SELECTEDVALUE('(Schedule) FACT- Schedule Data'[Activity ID]),
    ALLSELECTED('(Schedule) FACT- Schedule Data'[Activity ID])
)

To try and isolate the problem, I created a Card for CriticalEvent and put it next to the Slicer:

It works as long as the Card is connected to the Slicer. When I disconnect the Card, CriticalEvent fails to find the single value:

For some reason, I thought I should reconnect the Slicer to the table and try ALL in Total Float TEST to see if I could get it to ignore the Slicer that way.

Total Float TEST = 
VAR CriticalEvent = [CriticalEvent]
RETURN
    CALCULATE(
        IF(
            ABS(MIN('(Schedule) FACT- Schedule Data'[Float (Calendar Days)])) >
                MIN('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]),
            MIN('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]),
            ABS(MIN('(Schedule) FACT- Schedule Data'[Float (Calendar Days)]))
        ),
        ALL('(Schedule) FACT- Schedule Data'[Activity ID]),
        CONTAINSSTRING('(Schedule) FACT- Schedule Data'[CLPI Activities], CriticalEvent)
    )

Interestingly, the Table shows all Activity IDs now (so is ignoring the Slicer), but they all have the same Total Float TEST Value of -339, and I've got no idea why.

I feel like I'm getting close! I assume maybe I used ALL incorrectly?