cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michaelshparber Regular Visitor
Regular Visitor

SELECTCOLUMNS / FILTER return multiple values

Hello friends,

I have a set of date/time points (PointsInTime table).

For each point I need to show what event was translated on a TV at that point (Events table).

I used a calculated column in PointsInTime table:

EventName = SELECTCOLUMNS(FILTER(Events,Events[EventStartDateTime]<='PointsInTime'[SomeDateTime]&&Events[EventEndDateTime]>='PointsInTime'[SomeDateTime]),"AAA",[Event])
It works fine untill... there are overlapping events (see example photo & attached .pbix).
In this case it shows error:
"A table of multiple values was supplied where a single value was expected."
In case of overlapping events I don't really care which event to show.
So how do I select One of the values returned? I tried Max/Min(Event) but it didn't work.
Please Help
Thanks
Michael
.pbix file:
 
Annotation 2019-08-03 142132.png

 

Annotation 2019-08-03 143206.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Geradav Regular Visitor
Regular Visitor

Re: SELECTCOLUMNS / FILTER return multiple values

Hi @michaelshparber 

 

Try the following:

EventWithOverlap =
IF (
    CALCULATE (
        COUNTROWS ( EventsWithOverlap ),
        FILTER (
            EventsWithOverlap,
            EventsWithOverlap[EventStartDateTime] <= PointsInTime[SomeDateTime]
                && EventsWithOverlap[EventEndDateTime] >= PointsInTime[SomeDateTime]
        )
    ) > 1,
    CALCULATE (
        FIRSTNONBLANK ( EventsWithOverlap[Event], TRUE () ),
        FILTER (
            EventsWithOverlap,
            EventsWithOverlap[EventStartDateTime] <= PointsInTime[SomeDateTime]
                && EventsWithOverlap[EventEndDateTime] >= PointsInTime[SomeDateTime]
        )
    ),
    BLANK ()
)

Does that give you what you want?

Annotation 2019-08-03 165942.jpg

Let us know if that works for you @michaelshparber 

View solution in original post

Iamnvt Member
Member

Re: SELECTCOLUMNS / FILTER return multiple values

@michaelshparber 

you can try this as Calculated column:

EventWithOverlap = CALCULATE(FIRSTNONBLANK(EventsWithOverlap[Event],0),FILTER(EventsWithOverlap,EventsWithOverlap[EventStartDateTime]<='PointsInTime'[SomeDateTime]&&EventsWithOverlap[EventEndDateTime]>='PointsInTime'[SomeDateTime]))

 

View solution in original post

4 REPLIES 4
Mvignesh53 Member
Member

Re: SELECTCOLUMNS / FILTER return multiple values

I think you could use this Post. Its does a Time-bound Lookup too.

https://community.powerbi.com/t5/Desktop/DATE-RANGE-LOOKUP/td-p/420514

Highlighted
Geradav Regular Visitor
Regular Visitor

Re: SELECTCOLUMNS / FILTER return multiple values

Hi @michaelshparber 

 

Try the following:

EventWithOverlap =
IF (
    CALCULATE (
        COUNTROWS ( EventsWithOverlap ),
        FILTER (
            EventsWithOverlap,
            EventsWithOverlap[EventStartDateTime] <= PointsInTime[SomeDateTime]
                && EventsWithOverlap[EventEndDateTime] >= PointsInTime[SomeDateTime]
        )
    ) > 1,
    CALCULATE (
        FIRSTNONBLANK ( EventsWithOverlap[Event], TRUE () ),
        FILTER (
            EventsWithOverlap,
            EventsWithOverlap[EventStartDateTime] <= PointsInTime[SomeDateTime]
                && EventsWithOverlap[EventEndDateTime] >= PointsInTime[SomeDateTime]
        )
    ),
    BLANK ()
)

Does that give you what you want?

Annotation 2019-08-03 165942.jpg

Let us know if that works for you @michaelshparber 

View solution in original post

Iamnvt Member
Member

Re: SELECTCOLUMNS / FILTER return multiple values

@michaelshparber 

you can try this as Calculated column:

EventWithOverlap = CALCULATE(FIRSTNONBLANK(EventsWithOverlap[Event],0),FILTER(EventsWithOverlap,EventsWithOverlap[EventStartDateTime]<='PointsInTime'[SomeDateTime]&&EventsWithOverlap[EventEndDateTime]>='PointsInTime'[SomeDateTime]))

 

View solution in original post

michaelshparber Regular Visitor
Regular Visitor

Re: SELECTCOLUMNS / FILTER return multiple values

@Iamnvt  @Geradav 

FIRSTNONBLANK()!!!
Works great!
Thank you!
Michael

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,932)