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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
olepropell
New Member

Filter visuals with position data to show only current position per tracking unit

Hi. I use Direct Query and receive a dataset with positions from tracking units. Each tracking has a timestamp.

 

I have two visuals, a table and and a map.

 

What I want to achieve is a button for showing only the latest position per unit. 

 

Example: I want to move from this view:

Before.png

 

To this view:

After.png

Any assistance will be highly appreciated 🙂

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @olepropell ,

 

I created a slicer button and a measure to do this.

 

Create a table for the slicer mine has the following values:

 

Values

All Values
Latest Values

 

Now create the following measure:

Latest_Values =
VAR temp_table =
    SUMMARIZE (
        ALL ( 'Table'[TrackerID], 'Table'[Date], 'Table'[Lat], 'Table'[Long] ),
        'Table'[TrackerID],
        "DATE", MAX ( 'Table'[Date] )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Period Selection'[Values] ),
        "All Values", 1,
        IF (
            SELECTEDVALUE ( 'Table'[Date] )
                = MAXX (
                    FILTER ( temp_table, 'Table'[TrackerID] = MAX ( 'Table'[TrackerID] ) ),
                    [DATE]
                ),
            1
        )
    )

Now add this has filter on your visualizations and select all non blank vlaues

 

Result below and in attach PBIX file.

 
 

all_v.pnglates_v.png

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
olepropell
New Member

I am having trouble getting all the data into the temp-table. Transport and Trigger is collected from two help-tables, Knot is a measure in the main table (TrackingsDQ) while Course and Battery are regular data fields in the main table.

 

If I for example try to collect Transport from the help table dTransport this is what happens:

olepropell_0-1615726565066.png

 

Are there other and more straight-forward strategies for singeling out the latest rows? A more sophisticated time slicer or other things?

 

 

Hi, @olepropell 

According to your DAX formula, I think the problem exists in the SUMMARIZE() function you used, you can’t use a column from another table here:

v-robertq-msft_0-1615775849937.png

 

You can try to nest a RELATED() function in the outer layer of this column, but you should make sure the two tables must have a relationship first.

RELATED(dTransport[Transport])

What’s more, to get more detailed help from other experts, I suggest you to open a new case in the community. Because most communitors can’t see your question because it’s under another topic.

Thank you very much!

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-robertq-msft
Community Support
Community Support

Hi, @olepropell 

Have  MFelix’s reply helped you to solve this problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

If not, I suggest you to post some sample data or your test pbix file (without sensitive data) and we can help you in advance.

Thanks in advance!

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MFelix
Super User
Super User

Hi @olepropell ,

 

I created a slicer button and a measure to do this.

 

Create a table for the slicer mine has the following values:

 

Values

All Values
Latest Values

 

Now create the following measure:

Latest_Values =
VAR temp_table =
    SUMMARIZE (
        ALL ( 'Table'[TrackerID], 'Table'[Date], 'Table'[Lat], 'Table'[Long] ),
        'Table'[TrackerID],
        "DATE", MAX ( 'Table'[Date] )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Period Selection'[Values] ),
        "All Values", 1,
        IF (
            SELECTEDVALUE ( 'Table'[Date] )
                = MAXX (
                    FILTER ( temp_table, 'Table'[TrackerID] = MAX ( 'Table'[TrackerID] ) ),
                    [DATE]
                ),
            1
        )
    )

Now add this has filter on your visualizations and select all non blank vlaues

 

Result below and in attach PBIX file.

 
 

all_v.pnglates_v.png

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi MFelix.

 

This really helped and I am trying to implement this into my model now. Just to check: The temp table in the measure must be built with all columns I want to use in my visuals, correct?

 

In my example each tracking also have data for Knots, Course, Battery, Transport and Trigger.

Hi @olepropell ,

 

Not necessarily, you must use the ones that give you most information you need in this case the date, lat, long and ID, should be enough, if you see this does not return expected result then add the rest of the columns.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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