Using a measure as a slicer - cant figure it out!

[ansa_naz]

Hi all

I have the below tables:

Lifts table:

LiftID      Customer         LiftRef

1             A                        Lift1

2             A                       Lift2

3             B                        Lift3

4             C                        Lift4

5             D                       Lift5

6             E                        Lift6

7             E                        Lift7

8             F                        Lift8

9             F                        Lift9

Customer table:

Customer

A

B

C

D

E

F

Reports table:

LiftID        Report Date              Overdue

1              01/01/19                   Yes

1              15/06/18                   No

1              12/04/18                   No

2              15/09/18                   Yes

2              08/12/18                  No

4              01/01/19                  Yes

6              01/02/19                  No

I also have the below measure:

MLastOverdue = 
VAR _LastDate =
    MAX ( Reports[Report date] )
VAR _CustomersLifts =
    CALCULATETABLE (
        DISTINCT ( Lifts[Lift ID] ),
        ALL ( Lifts )
    )
RETURN
    IF (
        SELECTEDVALUE ( Lifts[Lift ID] )
            IN _CustomersLifts,
        IF (
            ISBLANK ( _LastDate ),
            "No report",
            CALCULATE (
                DISTINCT ( Reports[Overdue] ),
                Reports[Report date] = _LastDate
            )
        )
    )

I need to use the measure as a slicer but unsure how to do this? If anyone can help would be most appreciated. See below example PBIX file:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYL_-CFbONVzOLWDA

Many thanks for all help

Cheers

[v-danhe-msft · Accepted solution]

Hi @ansa_naz,

Based on my research, Power BI could not support measure as slicer currently, you may refer to below blog to have a work around:

https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

Regards,

Daniel He

[ansa_naz]

Hi @v-danhe-msft, thanks for your reply, I have been researching disconnected tables and slicers for a few hours now to try to replicate this into my file. I have created a disconnected table and slicer:

Overdue table:

Overdue                 Order

Yes                         1

No                          2

No Report              3

I have then created a measure, as below:

Overdue(Selected) = 

// This Variable is looking for the selected value from the Disconnected Slicer
// If nothing is selected then display "No Activity Selected"

VAR OverdueSelected = SELECTEDVALUE(Overdue[Overdue],"No Activity Selected")

// Now using an IF statement, we can then pass through the required values.

RETURN

// If nothing has been selected then return all values

IF(OverdueSelected = "No Activity Selected",[MLastOverdue],

// Else if a value is selected then pass that value to the calculate below

CALCULATE([MLastOverdue] = OverdueSelected)
)

If I add this measure into my table, and then select values from the Overdue slicer, my table is not getting 'sliced' - it just shows True/False values in the table but for every row. Ideally, if a user selects 'No Report' from the slicer, then only those rows where MLastOverdue='No Report' would display, etc. If no selection is made then every row would display. Any ideas how I can refine this second measure to slice my table?

See below my PBIX file:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYL_-CFbONVzOLWDA

Cheers for all help

[ansa_naz]

Further research into disconnected tables, shows that this is the way to resolve this issue. Cheers