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

Trying to use a measure as a filter

Hi all,

 

I am trying to use a measure as a filter. So far I can't get it to work. Let me show you my problem:

 

I have one field, 'transportation_cost', with three columns.

-------------------------------------------------------------

|   Posting Date    |    Shipment City    |   Cost per Unit   |

-------------------------------------------------------------

|    09/11/2019     |        Shanghai        |          0.91          |

|    10/11/2019     |       Amsterdam     |          0.34          |

|    11/11/2019     |        Shanghai        |          0.82          |

...

 

Here are the steps I want to apply to get my final filtered table:

 

1. Order this field by Posting Date and get the Shipment City with the most recent Posting Date...

 

2. Use this single value of Shipment City as a filter for the same, unfiltered 'transportation_cost' field.

 

For this specific example, the final table would look like this:

-------------------------------------------------------------

|   Posting Date    |    Shipment City    |   Cost per Unit   |

-------------------------------------------------------------

|    09/11/2019     |        Shanghai        |          0.91          |

|    11/11/2019     |        Shanghai        |          0.82          |

 

How do I do this in Power BI? I tried many things, including creating a measure for the most recent Shipment City and using that as filter for the final table:

First_shipment_city_from_vendor = SELECTCOLUMNS(TOPN(1,transportation_cost,transportation_cost[Posting Date], DESC),"Shipment City",[Shipment City])

 

Shipment_cost_from_shipment_city = VAR test = [First_shipment_city_from_vendor] RETURN CALCULATE(SUM(transportation_cost[Cost per Unit]), FILTER(ALL(transportation_cost[Shipment City], transportation_cost[Posting Date]), transportation_cost[Shipment City] = test))

 

This did not work, as I got errors in my resulting table visual complaining about the result not being a column but a table.

 

Any ideas on how to implement this?

Thanks in advance!

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Trying to use a measure as a filter

Hi @nickVEG ,
I believe this works.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

New table =
VAR _maxShipDate =
    LASTDATE ( ship[Posting Date] )
VAR _city =
    CALCULATE ( MAX ( ship[Shipment City] ), ship[Posting Date] = _maxShipDate )
VAR _table =
    CALCULATETABLE ( ship, ship[Shipment City] = _city )
RETURN
    _table


filtercity1.PNG

 





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

Proud to be a Super User!




Highlighted
Regular Visitor

Re: Trying to use a measure as a filter

Hi Nathaniel,

 

Thanks a lot for your time, I feel like I'm almost there. Your solution does seem to work, but partially. Somehow the resulting table is not being dynamically updated according to the shipment city. I created measures of each of the VARS in your query so I could track them in a card. The resulting query looks like this:

 

Cost_per_city =
VAR _latestShipmentCity = [Latest Shipment City]
VAR _costForCity = CALCULATETABLE(transportation_cost, transportation_cost[Shipment City] = _latestShipmentCity)
RETURN _costForCity

 

When I print [Latest Shipment City] in a card, it clearly shows 'Shanghai'. However, when I show the resulting table in a table visual, it doesn't seem to filter it right. The table doesn't seem to update dynamically according to the live measures (or even in-line calculations). Do you have any idea how to solve this?

Highlighted
Community Support
Community Support

Re: Trying to use a measure as a filter

Hi @nickVEG 

 

Try this one:

Table 3 = var a = CALCULATE(MAX(transportation_cost[Posting Date ]),ALL(transportation_cost))
var b = CALCULATE(MAX(transportation_cost[Shipment City]),FILTER(ALL(transportation_cost),[Posting Date ]=a))
Return
CALCULATETABLE(transportation_cost,FILTER(transportation_cost,transportation_cost[Shipment City]=b))

09.PNG 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Regular Visitor

Re: Trying to use a measure as a filter

Hi @v-diye-msft ,

 

Thanks for looking into this.

 

I implemented your solution, but the problem persists. I have a slicer that directly influences the variables and measures we use to get to the final table with only one shipment city, but these measures do not seem to dynamically edit the modelled table. In the last example I gave the shipment city measure was clearly 'Shanghai', but the table did not reflect this. Any ideas on what could be causing this?

Highlighted
Community Support
Community Support

Re: Trying to use a measure as a filter

Hi @nickVEG 

 

Would you mind sharing your dummy pbix that we can look into it?

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors