Slicer a visual table with a measure

[massotebernoull]

Hey guys

I am trying to do something real simple, but I don't get it.

I have a dimention table with data from schools:

SCHOOL

A

B

C

D

E

F

G

H

I

J

K

L

M

And I also have a table with interaction with some of those schools:

interact table

SCHOOL	date
A	08/03/2021
A	10/02/2021
A	10/10/2020
B	31/05/2021
B	20/05/2021
B	08/03/2020
E	14/03/2021
E	08/01/2021
E	17/08/2020
H	22/02/2021
H	17/05/2020

I created a measure that thells me if "@school" were contacted by year. It works pretty fine:

Interact measure =

IF(CALCULATE(

COUNT(Interact[school]),

filter(Interact, YEAR(Interacao[date]) = [YEAR_CR])

) > 0 , "interactec", "not interacted")

YEAR_CR is a measure based on year...

but the goal here is to create a slicer that can filter just the interactions or non interactions.

I tried something like theses videos:

https://www.youtube.com/watch?v=8Nt6sHJE_RE&ab_channel=GuyinaCube

and 

https://www.youtube.com/watch?v=AZAL-QPn5Zc&list=PLgweh3bybxsauJ1Q400J4e_ETbgOw7WSN&ab_channel=BIEli...

I'm not sure if this is the best way, but I tried to create an independent table 2x2:

FaceValue

Interact	1
not interacted	0

like those videos, but I had no sucess.

Please give me some suggestion!
Thank you

[v-yiruan-msft]

Hi @massotebernoull ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can create a measure as below:

Countofschool = 
VAR _selyear =
    SELECTEDVALUE ( 'Year'[Year] )
VAR _selstatus =
    SELECTEDVALUE ( 'Table'[Status] )
RETURN
    IF (
        _selstatus = "Interact",
        CALCULATE (
            COUNT ( Interact[school] ),
            FILTER ( Interact, YEAR ( 'interact'[date] ) = _selyear )
        ),
        0
    )

If the above one is not your expected one, please provide some sample data (exclude sensitive data) and your expected result with backend logic and special examples. It is better if you can share a simiplified pbix file with me. You can refer the following thread to upload your file in the community. Thank you.

How to upload PBI in Community

Best Regards

[massotebernoull]

It is almost that, your measures is really close to the ones I have. I need a slicer that tells me which school have interactions and wich schools have no interactions. Like this ilustration:

is it possible?

[v-yiruan-msft · Accepted solution]

Hi @massotebernoull ,

You can create a measure as below to judge whether the school is interactive or not, please find the details in the attachment.

Flag = 
VAR _selyear =
    SELECTEDVALUE ( 'Year'[Year] )
VAR _selstatus =
    SELECTEDVALUE ( 'Table'[Status] )
VAR _selschool =
    SELECTEDVALUE ( 'School'[SCHOOL] )
VAR _cshools =
    CALCULATETABLE (
        VALUES ( 'interact'[SCHOOL] ),
        FILTER ( 'interact', YEAR ( 'interact'[date] ) = _selyear )
    )
VAR _except =
    EXCEPT ( VALUES ( 'School'[SCHOOL] ), _cshools )
RETURN
    IF (
        ( _selstatus = "Interact"
            && _selschool IN _cshools )
            || ( ( _selstatus = "not interacted"
            && _selschool IN _except ) ),
        1
    )

2. Apply a visual-level filter (Flag is 1) on the table visual as below screenshot

Best Regards

[massotebernoull]

this flag measure is amazing! thank you!!

[amitchandak]

@massotebernoull , Not very clear. You need dynamic segmentation to filter a measure

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k