Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michaelshparber
Advocate V
Advocate V

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
Geradav
Responsive Resident
Responsive Resident

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
Continued Contributor
Continued Contributor

@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
Geradav
Responsive Resident
Responsive Resident

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 

Iamnvt
Continued Contributor
Continued Contributor

@michaelshparber 

you can try this as Calculated column:

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

 

@Iamnvt  @Geradav 

FIRSTNONBLANK()!!!
Works great!
Thank you!
Michael
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.