FILTER inside CONCATENATEX

[CarlaTP]

I'm new to Power Bi/DAX and having trouble getting my head around connecting some measures together.

I'm displaying which doctors operate at which hospitals today, according to a 4 week roster. All determined by when we have seen them in the past (each record is called an Episode). I have a Boolean in my Calendar table called Is Same As Today which my visual is filtered by.

For each Hospital I show the list of doctors with:

List of Doctors = 
    CONCATENATEX(
        VALUES('Episodes'[Doctor]),
        'Episodes'[Doctor],
        ", ",
        'Episodes'[Doctor],
        ASC
    )

What I want to do, is filter the values that go into the above CONCATENATEX so that we only see Doctors who were seen at least 2 times in the last 3 relevant dates.

I created this code with the help of DAX Studio, it gives the number of times the doctor was seen on the appropriate day in the last 12 weeks:

Seen In Three = 
SUMMARIZECOLUMNS (
    Episodes[Doctor],
    Episodes[Location],
    KEEPFILTERS (
        TREATAS (
            {
                1
            },
            CalendarRequested[Is Same As Today]
        )
    ),
    FILTER (
        CalendarRequested,
        CalendarRequested[Date]
            IN DATESINPERIOD (
                CalendarRequested[Date],
                TODAY (),
                -85,
                DAY
            )
    ),
    "Seen In Three", DISTINCTCOUNT(Episodes[Requested] )
)

How do I connect these two so that CONCATENATEX checks if Seen In Three is > 1?

[amitchandak · Accepted solution]

@CarlaTP , something like this

concatenateX(
ADDCOLUMNS( Summarize (calculatetable(Episodes, KEEPFILTERS (
TREATAS (
{
1
},
CalendarRequested[Is Same As Today]
)
),
FILTER (
CalendarRequested,
CalendarRequested[Date]
IN DATESINPERIOD (
CalendarRequested[Date],
TODAY (),
-85,
DAY
)
)) ,
Episodes[Doctor],
Episodes[Location]),

"Seen In Three", DISTINCTCOUNT(Episodes[Requested] )
) ,[Doctor])

[CarlaTP]

EDIT: Turns out it wasn't working perfectly, many attempts later and i have it fixed. Had to remove the ADDCOLUMNS and move the DISTINCTCOUNT into the SUMMARIZE. Changed the code below to show final working answer.

Thank you so much! I chucked the whole thing into a filter and it works perfectly now.

List of Doctors = 
    CONCATENATEX (
        FILTER (
            SUMMARIZE (
                CALCULATETABLE (
                    Episodes,
                    KEEPFILTERS (
                        TREATAS (
                            {
                                1
                            },
                            CalendarRequested[Is Same As Today]
                        )
                    ),
                    KEEPFILTERS (
                        TREATAS (
                            DATESINPERIOD (
                                CalendarRequested[Date],
                                TODAY (),
                                -85,
                                DAY
                            ),
                            CalendarRequested[Date]
                        )
                    )
                ),
                Episodes[Doctor],
                Episodes[Location],
                "Seen In Three", DISTINCTCOUNT ( Episodes[Requested] )
            ),
            [Seen In Three] > 1
        ),
        [Doctor],
        ", ",
        [Doctor]
    )