topic Re: Extract value from a column applying filters in DAX Commands and Tips

Extract value from a column applying filters

CCUK22 — Sat, 04 Jun 2022 09:36:36 GMT

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

Re: Extract value from a column applying filters

tamerj1 — Sat, 04 Jun 2022 10:32:27 GMT

Hi CCUK22

by the last month's entry

do you mean previous month ore kast month in filter context? How does your visual look like?

Re: Extract value from a column applying filters

CCUK22 — Sat, 04 Jun 2022 17:53:21 GMT

Hi, it is the last month in the filter context.

Re: Extract value from a column applying filters

CCUK22 — Sat, 04 Jun 2022 17:55:40 GMT

the last month in the filter context is March. In June the last month will be April and so on...

Re: Extract value from a column applying filters

Anonymous — Wed, 08 Jun 2022 05:32:16 GMT

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

Re: Extract value from a column applying filters

CCUK22 — Sat, 11 Jun 2022 21:00:55 GMT

Hello Yingyinr,

I just saw your reply today. Thank you for taking the time. I will test it and let you know.

Regards,

Carlos

Re: Extract value from a column applying filters

CCUK22 — Wed, 15 Jun 2022 08:27:54 GMT

Hello Yingyinr,

The solution has worked! I pulled the correct table and columns, and added a date and region slicer.

Thank you!

Carlos