cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CCUK22
Frequent Visitor

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

2 ACCEPTED SOLUTIONS

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.

yingyinr_1-1654666253296.png

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

 

yingyinr_0-1654666180226.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hello Yingyinr,

 

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

 

Regards,

Carlos

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

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?

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

CCUK22
Frequent Visitor

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

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.

yingyinr_1-1654666253296.png

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

 

yingyinr_0-1654666180226.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Yingyinr,

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

Thank you!

Carlos

Hello Yingyinr,

 

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

 

Regards,

Carlos

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.