Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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
Proud to be a Super User!
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))
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?
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |