Extract value from a column applying filters

[CCUK22]

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

[Anonymous · Accepted solution]

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

[tamerj1]

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?

[CCUK22]

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

[CCUK22]

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

[Anonymous · Accepted solution]

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

[CCUK22]

Hello Yingyinr,

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

Thank you!

Carlos

[CCUK22 · Accepted solution]

Hello Yingyinr,

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

Regards,

Carlos