topic Re: FILTER inside CONCATENATEX in DAX Commands and Tips

FILTER inside CONCATENATEX

CarlaTP — Thu, 03 Feb 2022 09:40:42 GMT

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?

Re: FILTER inside CONCATENATEX

amitchandak — Thu, 03 Feb 2022 09:55:50 GMT

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

Re: FILTER inside CONCATENATEX

CarlaTP — Fri, 04 Feb 2022 08:45:11 GMT

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