Forum Discussion
FILTER inside CONCATENATEX
- 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 , 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])
- CarlaTP4 years agoFrequent Visitor
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] )