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
Anonymous
Not applicable

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
Nathaniel_C
Super User
Super User

Hi @Anonymous ,
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!




Anonymous
Not applicable

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?

Hi @Anonymous 

 

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.
Anonymous
Not applicable

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?

Hi @Anonymous 

 

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
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.