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 All,
I need to marked several dates range in auto-genered [Calendar_Table] to use them like a filter for different table with not the same granularity of dates. This range should be determinating from Slicer value.
My action in next steps
1.Create measure to determinate last date in one of the table (in one of the source it means 1st date in month)
DT_LastDate_DS_TV = calculate(max(DS_TV_Audience[DT_DATE_TXT]);all(DS_TV_Audience))
2. Create measures:
DT_0_PeriodStart =
VAR periodcount = calculate(DISTINCTCOUNT(DIC_Calendar[DT_YYYY-MM]);DIC_Calendar)
VAR periodFiltred = CALCULATE(MIN(DIC_Calendar[DT_DATE_TXT]);DIC_Calendar)
VAR periodDefault = value([DT_LastDate_DS_TV])-value(mid([DT_LastDate_DS_TV];7;2))
RETURN
if(periodcount > 1; periodDefault;periodFiltred)
DT_0_PeriodEnd =
VAR periodcount = calculate(DISTINCTCOUNT(DIC_Calendar[DT_YYYY-MM]);DIC_Calendar)
VAR periodFiltred = CALCULATE(max(DIC_Calendar[DT_DATE_TXT]);DIC_Calendar)+6
VAR periodDefault = [DT_LastDate_DS_TV]+31
RETURN
if(periodcount > 1; periodDefault;periodFiltred)
3. Create slicer for [DT_YYYY-MM] column from DIC_CALENDAR table
4. Create new column in DIC_CALENDAR
DT_Period_0 =
VAR periodDefault_min = calculate(min(DIC_Calendar[DT_DATE]);Filter(DIC_Calendar;DIC_Calendar[DT_DATE_TXT]=[DT_LastDate_DS_TV]))
VAR periodDefault_max = calculate(max(DIC_Calendar[DT_DATE]);Filter(DIC_Calendar;DIC_Calendar[DT_DATE_TXT]=[DT_LastDate_DS_TV]))
VAR periodFiltred_min = [DT_0_PeriodStart]
VAR periodFiltred_max = [DT_0_PeriodEnd]
VAR countelement = [CHK_FiltredPeriod]
RETURN
if([CHK_FiltredPeriod] = 1;TRUE;FALSE)
If I put [DT_0_PeriodStart] & [DT_0_PeriodEnd] use in visual, they correct determinate range of dates, which should be marked in case of used and not used slicer. But in column [DT_Period_0] it always give me FALSE result.
ALTERNATIVE WAY
1. I Create measures for each KPI
KPI_AvRch% =
VAR periodDefault_min = value([DT_LastDate_DS_TV])-value(mid([DT_LastDate_DS_TV];7;2))
VAR periodDefault_max = [DT_LastDate_DS_TV]+31
VAR periodFiltred_min = CALCULATE(MIN(DIC_Calendar[DT_DATE_TXT]);DIC_Calendar)
VAR periodFiltred_max = CALCULATE(MAX(DIC_Calendar[DT_DATE_TXT]);DIC_Calendar)+6
RETURN
IF (
ISFILTERED(DIC_Calendar[DT_YYYY-MM]);
calculate(SUM(DS_TV_Audience[AvRch%]);filter(DIC_Calendar;DIC_Calendar[DT_DATE_TXT]>=periodFiltred_min&&DIC_Calendar[DT_DATE_TXT]<=periodFiltred_max));
calculate(SUM(DS_TV_Audience[AvRch%]);filter(DIC_Calendar;DIC_Calendar[DT_DATE_TXT]>=periodDefault_min&&DIC_Calendar[DT_DATE_TXT]<=periodDefault_max))
)
But this way is not comfortable, because I need a lot of different statistics and range selections.
Solved! Go to Solution.
Hi @Denis_Slav .
You cannot use measure to create or filter out table columns directly, meaning that if you are using a measure to create a new value in a column this will always return incorrect values due to the context.
Measures are calculated when they are called and always depeding on the context so since a table is a fixed when you use a measure to calculate some information the result will always be the same.
Do you have a relationship between your calendar table and the other tables?
What is the exact result you want to achieve sometimes having the correct connection and the right slicers in place is the best way to make the calculations.
Can you share a sample file and expected result?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Denis_Slav .
You cannot use measure to create or filter out table columns directly, meaning that if you are using a measure to create a new value in a column this will always return incorrect values due to the context.
Measures are calculated when they are called and always depeding on the context so since a table is a fixed when you use a measure to calculate some information the result will always be the same.
Do you have a relationship between your calendar table and the other tables?
What is the exact result you want to achieve sometimes having the correct connection and the right slicers in place is the best way to make the calculations.
Can you share a sample file and expected result?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |