How to calculate number of selected values after selecting (CTRL+click) datapoints in table

[RafalMonka]

Hi everyone,

in PowerBI dashboard we have 2 visuals: 

1. slicer for field TaskName (full list contains 100 items)

2. standard table with column TaskName and with multiple rows.

When we select a few items in a slicer, then the rows in the table are limited to these selected TaskNames, for instance 5 items.

Then we use CTRL+click in the table to select only 2 rows of TaskNames.

What DAX expression can be used to calculate number of rows selected in a slicer (these 5)?

When CALCULATE(DISTINCTCOUNT(...), ALLSELECTED(...)) is used, then it return 2.

When CALCULATE(DISTINCTCOUNT(...), ALL(...)) is used, it return 100.

[bcdobbs]

For others reading here is an explanation to go with the solution PBIX posted earlier.

Requirement was to count the number of tasks selected in a slicer and the number of tasks then selected in a visual.

1) Started with the following table called Task:

2) Created a copy of the table called SlicerTask (SlicerTask = Task). Make sure PowerBi doesn't auto create relationships between the two tables. They need to be disconnected.

3) Added the following measures to the Task table
(slightly more optimised DAX than featured in the original in the PBIX I posted):

FilterSelected = 
    CALCULATE(
        INT ( NOT ( ISEMPTY( Task ) ) ),
        TREATAS ( 
            VALUES ( SlicerTask[TaskId] ), 
            Task[TaskId] 
        )
    )

Number Selected in Visual = 
CALCULATE (
    COUNTROWS( Task ),
    TREATAS (
        VALUES ( SlicerTask[TaskId] ), 
        Task[TaskId]
    )
)

Number Selected in Slicer = COUNTROWS ( SlicerTask )

FilterSelected moves the filter from the SlicerTask table to the Task table. Then under the filter context of each row of the visual its put on checks if the Task table is empty. INT NOT ISEMPTY returns 1 if it should display the row in the visual and 0 is not.

4) Put the FilterSelected measure on the visual level filter of the display table and set to "Is 1":

5) Put the two count measures in cards or use to build other measures.

Care would need to be taken in adding other visuals as the FilterSelected measure would need to be added to them as well.

[bcdobbs · Accepted solution]

Got it...

Easiest to see solution in pbix file... Solution 

Key to it is the measure that moves the filter from the disconnected table over to the real table using TREATAS which is placed on the tables visual level filter.

[RafalMonka]

Thank you very much. We'll need to introduce separated table for slicer and then your solution works!

[bcdobbs]

To the best of my understanding of DAX (if others disagree please do correct me!) a measure on card is unable to determine where the filter context it's seeing is coming from. So you can't directly do that with a single measure.

You could however use two card arranged next to each other one with the visual interaction turned off and one with it turned on?

[RafalMonka]

Thanks again. I've given text with variables only as a example "You have selected 2 of 4 tasks", but our requirement is slightly more complicated and we definetely need variables to calculate them further in DAX. If it is not possible, we'll have to re-work this req.

[bcdobbs]

Wondering if could do something with a disconnected table to get this effect. I'm not by a computer at moment but will give it a go this evening.

[bcdobbs]

I don't think it's a measure issue. 
If you use your ALLSELECTED version inside the table it will work fine.

If you've put the measure in a card then you need to prevent the table from interacting with it...

Format >> Edit Interactions
Click the table and then click the circle with a line through it on the measure card.

[RafalMonka]

Thank you for suggestion, but unfortunatelly it doesn't exactly address the issue.

When used in a table, as you have shown, it works.

But we need a measure (DAX formula) in which we can take number of selected items (one variable with value 2 and second with value 4 in your example). This measure must be used as a field in a separate visual (Card), showing text e.g. "You have selected 2 of 4 tasks".