Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have Created a calculated measure to get the min date for a particular filter e.g. Calculate(min(date), filter(countryname="US"))
This give min date based on the date table and not taking the filter into consideration
E.g. Min date for US is 08092017
This is expected, but am getting only the min date in the date table which is 01011970
Tried excluding the date table in the Expression..and surfed over net no luck
Thanks,
Aravind
Solved! Go to Solution.
As per my reply above, I use date from date table and date_id is alone present in the fact.
I was able to resolve by creating the dim column within the fact and created the metric with the expression worked for the formula
Calculate(min(full_date),filter(fact,fact[country_name]))
@Anonymous Not sure what you have done. I did the same thing I am getting min date based on filter criteria. Please see below mentioned screenshot.
Measure =
CALCULATE ( Min ( Sheet1[Date].[Date] ), FILTER(Sheet1,Sheet1[Name]="A" ))
I see you use one table here, in my case it uses two tables, eg: date and country table. when i tried to filter from same table i get the output correctly
Hi @Anonymous ,
If above formula does not work, could you please describle the main fields of each tables and the relations between tables simply? Please don't have any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-lid-msft ,
The above formula is not working.
I have two dimension tables joining via fact. As mentioned above i was trying to retrieve the minimum date by country.
Dimensions used:
dim_country
dim_date
fact:
fact_sales
Example:
Date_ID | Country | Date | Min Date | Sum(Sales) |
01012018 | US | 01/01/2018 | 01/01/1970 | 3 |
02012018 | US | 02/01/2018 | 01/01/1970 | 3 |
03012018 | US | 03/01/2018 | 01/01/1970 | 3 |
04012018 | US | 04/01/2018 | 01/01/1970 | 3 |
Here I get the correct ID and value, but am facing issue when i was trying to get the Min or Max date by country which is returning the min and max date in the dim_date table. (Above is sample value created to showcase the result am getting)
Hi @Anonymous ,
We could try to use the following measure to meet your requirement:
MinDateByCountry =
CALCULATE (
MIN ( 'fact_sales'[Date] ),
FILTER (
ALLSELECTED ( 'fact_sales' ),
'fact_sales'[Country] IN FILTERS ( 'dim_country'[Country] )
)
)
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
As per my reply above, I use date from date table and date_id is alone present in the fact.
I was able to resolve by creating the dim column within the fact and created the metric with the expression worked for the formula
Calculate(min(full_date),filter(fact,fact[country_name]))
@Anonymous did you try something like this?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |