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
CHaines
Helper I
Helper I

Dynamic sized table showing current /previous items

Help please. I want to show a list of the passengers for the selected taxi route/date, and a list of passengers for the same route the previous day. This list is not always the same length. I also want the table to be blank when no route is selected.  Any help?

Currently I have 2 visuals
- First visual shows Table_journey_detail[RouteID] and Table_journey_detail[Date], 
- Second visual shows only Table_individual_details[PersonID]

I have 2 problems with this 1) when no route/date is selected on the first visual, the second visual shows every possible PersonID, and 2) my previous passenger list is showing current passengers.

NOTE: I cannot use concatenatex as I want selecting the PersonID to filter the first visual 

Data Available

Table_journey_detail

Date

RouteID

_#Passengers

Cost

OtherData

10/2/2020

A1

3

£6.00

True

10/2/2020

B2

2

£4.00

False

11/2/2020

A1

4

£7.00

False

11/2/2020

B2

2

£4.00

True

12/2/2020

A1

3

£6.00

True

 

Table_individual_details

Date

RouteID

PersonID

OtherData

10/2/2020

A1

Ewan

Dc

10/2/2020

A1

Niamh

Rb

10/2/2020

A1

Cillian

Tyej

10/2/2020

B2

Saiorse

Etuk

10/2/2020

B2

Aodh

Ril

11/2/2020

A1

Ewan

Il

11/2/2020

A1

Aiofe

Yil

11/2/2020

A1

Niamh

Hng

11/2/2020

A1

Ciaran

iov

11/2/2020

B2

Aisling

Ag

11/2/2020

B2

Bronagh

ARG

12/2/2020

A1

Brigid

Y

12/2/2020

A1

Niamh

RYIYL

12/2/2020

A1

Cillian

TUI


Expected Output:


Journey selected – A1-10/2/2020

Most Recent Journey

Previous Journey

Ewan

 

Niamh

 

Cillian

 


Journey selected – A1-11/2/2020

Most Recent Journey

Previous Journey

Ewan

Ewan

Aiofe

Niamh

Niamh

Cillian

Ciaran

 


Journey selected – A1-12/2/2020

Most Recent Journey

Previous Journey

Brigid

Ewan

Niamh

Aiofe

Cillian

Niamh

 

Ciaran

 

Journey selected – None (empty table)

Most Recent Journey

Previous Journey

 

2 ACCEPTED SOLUTIONS

@CHaines 

 

So here is the solution on how to make it so that nothing appears in

your output when both the Date & RouteID are not selected.

 

Step 1 - Create a measure to check if BOTH the Date AND RouteID are being filtered.

 

WinterMist_0-1677686927412.png

 

Step 2 - Select the "OUTPUT" table visual, and add the measure as a Filter on the visual.

 

WinterMist_1-1677687018717.png

 

When the Date & RouteID are not selected, or only 1 of them is selected, no results are shown in the OUTPUT.

 

WinterMist_2-1677687127897.png

 

However, when both Date AND RouteID are selected, then the OUTPUT shows results:

 

WinterMist_3-1677687221329.png

 

 

NOTES:

- In this case, I am able to select the Journey (without using the slicers) and it still works, but that is because I'm only using a single table in the model for all the data.  Depending on your model, you might not be able to do this, and be forced to use slicers.

 

- You will also notice that the output is sorted in alphabetical order.  That's because per the data provided, there is nothing else to sort the name by.  So it will not appear in the same order as it did in the full table.

 

Full Table Order: Ewan, Niamh, Cillian

OUTPUT Order: Cillian, Ewan, Niamh

 

I'll get back to you on the Previous Journey, but this should get you started.

 

Regards,

Nathan

View solution in original post

tamerj1
Super User
Super User

Hi @CHaines 
Please refer to attached sample file with the proposed solution. Hope this is what you're looking for

1.png2.png

Most Recent Journey = 
VAR CurrentIndex = SELECTEDVALUE ( 'Index'[Value] )
VAR T1 = VALUES ( individual_details[PersonID] )
VAR T2 = 
    ADDCOLUMNS ( 
        T1, 
        "@Index", RANKX ( T1, [PersonID],, ASC, Dense )
    )
VAR T3 = FILTER ( T2, [@Index] = CurrentIndex )
RETURN
    IF (
        HASONEVALUE ( journey_detail[Date] ) && HASONEVALUE ( journey_detail[RouteID] ),
        MAXX ( T3, [PersonID] )
    )
Previous Journey = 
VAR CurrentIndex = SELECTEDVALUE ( 'Index'[Value] )
VAR CurrentDate = SELECTEDVALUE ( journey_detail[Date] )
VAR T1 = 
    CALCULATETABLE ( 
        VALUES ( individual_details[PersonID] ), 
        REMOVEFILTERS ( ),
        VALUES ( individual_details[RouteID] ),
        individual_details[Date] = CurrentDate - 1 
    )
VAR T2 = 
    ADDCOLUMNS ( 
        T1, 
        "@Index", RANKX ( T1, [PersonID],, ASC, Dense )
    )
VAR T3 = FILTER ( T2, [@Index] = CurrentIndex )
RETURN 
    MAXX ( T3, [PersonID] )

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

Hi @CHaines 
Please refer to attached sample file with the proposed solution. Hope this is what you're looking for

1.png2.png

Most Recent Journey = 
VAR CurrentIndex = SELECTEDVALUE ( 'Index'[Value] )
VAR T1 = VALUES ( individual_details[PersonID] )
VAR T2 = 
    ADDCOLUMNS ( 
        T1, 
        "@Index", RANKX ( T1, [PersonID],, ASC, Dense )
    )
VAR T3 = FILTER ( T2, [@Index] = CurrentIndex )
RETURN
    IF (
        HASONEVALUE ( journey_detail[Date] ) && HASONEVALUE ( journey_detail[RouteID] ),
        MAXX ( T3, [PersonID] )
    )
Previous Journey = 
VAR CurrentIndex = SELECTEDVALUE ( 'Index'[Value] )
VAR CurrentDate = SELECTEDVALUE ( journey_detail[Date] )
VAR T1 = 
    CALCULATETABLE ( 
        VALUES ( individual_details[PersonID] ), 
        REMOVEFILTERS ( ),
        VALUES ( individual_details[RouteID] ),
        individual_details[Date] = CurrentDate - 1 
    )
VAR T2 = 
    ADDCOLUMNS ( 
        T1, 
        "@Index", RANKX ( T1, [PersonID],, ASC, Dense )
    )
VAR T3 = FILTER ( T2, [@Index] = CurrentIndex )
RETURN 
    MAXX ( T3, [PersonID] )

Some nifty work in here, thank you @tamerj1 ! It will take me a little while to figure out as well, but I think I'm getting the gist of what you've done here. It certainly works for displaing the current & previous values. I hope the product owner likes it XD

 

Sorry for moving the solution from you @WinterMist - it was a helpful guide and a tip to a functionality that I didn't know existed.

WinterMist
Impactful Individual
Impactful Individual

@tamerj1 

 

Brilliant, as usual.  🙂

 

I can see that it's working from your screenshots, but I'm going to have to study this one for a while.

 

Regards,

Nathan

@WinterMist 

Yes but caanot be used to filter the first visual as requested by the post originator. 

WinterMist
Impactful Individual
Impactful Individual

@CHaines 

 

Are you by chance able to share what the model looks like?

Are both these visuals just coming from 1 single table or from multiple tables?

 

Also, is it absolutely required that the first table visual be used to filter the second table visual?  This doesn't always work as desired because you are filtering entire table rows rather than isolated fields.  Would it be acceptable to use slicers for Date & RouteID instead?

 

Regards,

Nathan

Hi. Cheers for the questions. The 2 data tables are linked through a common merged column of rank(date)+RouteID in a many (Table_journey_detail) to one (Table_individual_details) relationship. RouteID and PersonID have a many to many relationship - a number of the peple take more than one route. Most routes have more than one person.

My end use datasets will likely have circa 10k routeIDs and circa 30k personIDs. We have a slicer for the date (good) and routeID (not so good) on the page: With so many RouteIDs, the slicer isn't useful for interogating the data this way. The product owner really wants the visuals to be crossfiltering like this as the "other data" will be used in each visual to filter the sets to find persons and journeys of interest. I can think of alternate ways of organising the page, but the product owner does not like them.

WinterMist
Impactful Individual
Impactful Individual

OK, I've taken the following steps to match your model...

 

1) Created a MergedKey calculated column for both 'Journey' & 'Individual' tables.

WinterMist_0-1677689706564.png

WinterMist_1-1677689735226.png

 

As a result, the tables now look like this...

 

'Journey'

WinterMist_2-1677689800535.png

 

'Individual'

WinterMist_3-1677689840370.png

 

2) Joined the 2 tables together in the model.

 

NOTE: You had mentioned that the 'Journey' table was the "many" side, but in my model it's the "1" side of the relationship.  Hopefully I haven't completely messed this up.

 

WinterMist_4-1677689939580.png

 

3) Updated the "JOURNEY" table visual so that it now pulls from the 'Journey' data table (instead of previously pulling from the same 'Individual' table).

 

WinterMist_5-1677690214722.png

 

4) Updated the slicer filter measure created earlier to reference 'Journey' columns instead of 'Individual' columns.

 

WinterMist_7-1677690372843.png

 

5) Removed slicers from the solution to try to to meet the requirements.

 

NOTES:

- You can select any journey record from the JOURNEY table visual & it will filter the INDIVIDUAL table as well as the OUTPUT because filters flow from the 1-side ('Journey') to the *-side ('Individual').  So your original requirement works.  But if you need to do the reverse (select an item in the INDIVIDUAL table visual to filter the JOURNEY table visual), that will not work with the current model.

 

Here is an example of your requirement to select a JOURNEY record & it properly shows the OUTPUT.  In this example, I selected the JOURNEY record for: 11/2/2020 & A1.  As mentioned previously the OUTPUT table is sorted alphabetically because no other option is available.

 

WinterMist_8-1677690743927.png

 

Regards,

Nathan

 

@CHaines 

 

So here is the solution on how to make it so that nothing appears in

your output when both the Date & RouteID are not selected.

 

Step 1 - Create a measure to check if BOTH the Date AND RouteID are being filtered.

 

WinterMist_0-1677686927412.png

 

Step 2 - Select the "OUTPUT" table visual, and add the measure as a Filter on the visual.

 

WinterMist_1-1677687018717.png

 

When the Date & RouteID are not selected, or only 1 of them is selected, no results are shown in the OUTPUT.

 

WinterMist_2-1677687127897.png

 

However, when both Date AND RouteID are selected, then the OUTPUT shows results:

 

WinterMist_3-1677687221329.png

 

 

NOTES:

- In this case, I am able to select the Journey (without using the slicers) and it still works, but that is because I'm only using a single table in the model for all the data.  Depending on your model, you might not be able to do this, and be forced to use slicers.

 

- You will also notice that the output is sorted in alphabetical order.  That's because per the data provided, there is nothing else to sort the name by.  So it will not appear in the same order as it did in the full table.

 

Full Table Order: Ewan, Niamh, Cillian

OUTPUT Order: Cillian, Ewan, Niamh

 

I'll get back to you on the Previous Journey, but this should get you started.

 

Regards,

Nathan

I had never before seen the option to add a filter to a visual like this. Fantastic stuff! I can construct a measure to place in here that uses something like  the rank(date) -1 as I am using for other values. Amazing 😄

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.

Top Solution Authors