Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
Hello Yingyinr,
I just saw your reply today. Thank you for taking the time. I will test it and let you know.
Regards,
Carlos
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.
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.
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,
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
23 |