Forum Discussion

CarlaTP's avatar
CarlaTP
Frequent Visitor
4 years ago
Solved

FILTER inside CONCATENATEX

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's avatar
    amitchandak
    4 years ago

    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])

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      CarlaTP
      Frequent Visitor
      4 years ago

      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]
    )