Forum Discussion

CCUK22's avatar
CCUK22
Frequent Visitor
4 years ago
Solved

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 Ave...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
    CCUK22
    4 years ago

    Hello Yingyinr,

     

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

     

    Regards,

    Carlos

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
CCUK22
Frequent Visitor
4 years ago

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

  • CCUK22's avatar
    CCUK22
    Frequent Visitor
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        CCUK22
        Frequent Visitor
        4 years ago

        Hello Yingyinr,

         

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

         

        Regards,

        Carlos