Forum Discussion
Extract value from a column applying filters
Dear Power Community,
I am trying to get metrics for some CARDs on PBI. Can you help to write a DAX metric? Then, I can use the expression for other cards.
I need to extract from the column AveragePriceDetachedHouse (there is no need to calculate the average, the value in the cell is already the average) at fact_HouseMarket; filtered by the last month's entry from column Date at dim_Period; filtered by England from column nam_RegionName at dim_Region. The dataset is updated on 1st of each month.
Thanks
Carlos
Hi CCUK22 ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. Assume that the proper relationships have been created among these three tables.
Then you can create a measure as below:
Measure = VAR _maxdate = CALCULATE ( MAX ( 'fact_HouseMarket'[Date] ), FILTER ( ALLSELECTED ( 'fact_HouseMarket' ), 'fact_HouseMarket'[Region ID] = SELECTEDVALUE ( 'fact_HouseMarket'[Region ID] ) ) ) RETURN CALCULATE ( MAX ( 'fact_HouseMarket'[AveragePriceDetachedHouse] ), FILTER ( 'fact_HouseMarket', 'fact_HouseMarket'[Date] = _maxdate ) )If the above one can't help you get the desired result, please provide some sample data in your tables fact_HouseMarket, dim_Period and dim_Region(exclude sensitive data) with Text format and your expected result with backend logic and special examples? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Best Regards
Hello Yingyinr,
I just saw your reply today. Thank you for taking the time. I will test it and let you know.
Regards,
Carlos
