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.
Good day,
1st post here, and I'm sort of a newbie.... I've tried to search for this topic to no avail.
I've come to love the usage of Disconnected Tables, to slice and dice my data by Calulated Measures...
However, I would also like to apply this solution by the Date Hierarchy created by Power BI... For instance, I would like to push a button to show, Yearly, or Monthly, or Quarterly..... and so on.
I've set-up my disconnected table for the date frequency, w/index... However, i need an assist in DAX to segment the dates in my table, by PBI's Date Hierarchy. I've been trying the Switch Funcion, but can't figure it out.
Is this possible??
Solved! Go to Solution.
Hi @qbarnes ,
We can create a disconnected date table as a workaround, but we cannot hide the column as the drill down/up feature
Date Axis =
var c = CALENDAR(MIN('Data'[Date]),MAX('Data'[Date]))
var YearTable = DISTINCT(SELECTCOLUMNS(c,"Year",YEAR([Date])))
var QuarterlyTable = DISTINCT(SELECTCOLUMNS(c,"Quarter",QUARTER([Date])))
var BlankQuarterlyTable = DATATABLE("Quarter",INTEGER,{{}})
var MonthlyTable = DISTINCT(SELECTCOLUMNS(c,"Month",MONTH([Date])))
var BlankMonthlyTable = DATATABLE("Month",INTEGER,{{}})
var DailyTable = DISTINCT(SELECTCOLUMNS(c,"Day",Day([Date])))
var BlankDailyTable = DATATABLE("Day",INTEGER,{{}})
return
UNION(ADDCOLUMNS(CROSSJOIN(YearTable,BlankQuarterlyTable,BlankMonthlyTable,BlankDailyTable),"Slicer","Yearly"),
ADDCOLUMNS(CROSSJOIN(YearTable,QuarterlyTable,BlankMonthlyTable,BlankDailyTable),"Slicer","Quarterly"),
ADDCOLUMNS(CROSSJOIN(YearTable,QuarterlyTable,MonthlyTable,BlankDailyTable),"Slicer","Monthly"),
ADDCOLUMNS(CROSSJOIN(YearTable,QuarterlyTable,MonthlyTable,DailyTable),"Slicer","Daily"))
Measure to show the data:
Show Data =
SWITCH (
SELECTEDVALUE ( 'Date Axis'[Slicer] ),
"Yearly", CALCULATE (
SUM ( 'Data'[Value] ),
FILTER ( 'Data', YEAR ( [Date] ) IN FILTERS ( 'Date Axis'[Year] ) )
),
"Quarterly",
CALCULATE (
SUM ( 'Data'[Value] ),
FILTER ( 'Data', YEAR ( [Date] ) IN FILTERS ( 'Date Axis'[Year] ) && QUARTER([Date]) in FILTERS('Date Axis'[Quarter] )
)),
"Monthly", CALCULATE (
SUM ( 'Data'[Value] ),
FILTER ( 'Data', YEAR ( [Date] ) IN FILTERS ( 'Date Axis'[Year] ) && QUARTER([Date]) in FILTERS('Date Axis'[Quarter]) && MONTH([Date]) in FILTERS('Date Axis'[Month])
)),
"Yearly",
CALCULATE (
SUM ( 'Data'[Value] ),
FILTER ( 'Data', YEAR ( [Date] ) IN FILTERS ( 'Date Axis'[Year] )&& QUARTER([Date]) in FILTERS('Date Axis'[Quarter]) && MONTH([Date]) in FILTERS('Date Axis'[Month]) && DAY([Date]) in FILTERS('Date Axis'[Day])
)
)
)
Consider of that we need to change every measure to meet the date axis or we can let the axis table crossjoin with every possible date, we still suggest use bookmark as an effective solution.
Best regards,
Hi @qbarnes ,
We can create a disconnected date table as a workaround, but we cannot hide the column as the drill down/up feature
Date Axis =
var c = CALENDAR(MIN('Data'[Date]),MAX('Data'[Date]))
var YearTable = DISTINCT(SELECTCOLUMNS(c,"Year",YEAR([Date])))
var QuarterlyTable = DISTINCT(SELECTCOLUMNS(c,"Quarter",QUARTER([Date])))
var BlankQuarterlyTable = DATATABLE("Quarter",INTEGER,{{}})
var MonthlyTable = DISTINCT(SELECTCOLUMNS(c,"Month",MONTH([Date])))
var BlankMonthlyTable = DATATABLE("Month",INTEGER,{{}})
var DailyTable = DISTINCT(SELECTCOLUMNS(c,"Day",Day([Date])))
var BlankDailyTable = DATATABLE("Day",INTEGER,{{}})
return
UNION(ADDCOLUMNS(CROSSJOIN(YearTable,BlankQuarterlyTable,BlankMonthlyTable,BlankDailyTable),"Slicer","Yearly"),
ADDCOLUMNS(CROSSJOIN(YearTable,QuarterlyTable,BlankMonthlyTable,BlankDailyTable),"Slicer","Quarterly"),
ADDCOLUMNS(CROSSJOIN(YearTable,QuarterlyTable,MonthlyTable,BlankDailyTable),"Slicer","Monthly"),
ADDCOLUMNS(CROSSJOIN(YearTable,QuarterlyTable,MonthlyTable,DailyTable),"Slicer","Daily"))
Measure to show the data:
Show Data =
SWITCH (
SELECTEDVALUE ( 'Date Axis'[Slicer] ),
"Yearly", CALCULATE (
SUM ( 'Data'[Value] ),
FILTER ( 'Data', YEAR ( [Date] ) IN FILTERS ( 'Date Axis'[Year] ) )
),
"Quarterly",
CALCULATE (
SUM ( 'Data'[Value] ),
FILTER ( 'Data', YEAR ( [Date] ) IN FILTERS ( 'Date Axis'[Year] ) && QUARTER([Date]) in FILTERS('Date Axis'[Quarter] )
)),
"Monthly", CALCULATE (
SUM ( 'Data'[Value] ),
FILTER ( 'Data', YEAR ( [Date] ) IN FILTERS ( 'Date Axis'[Year] ) && QUARTER([Date]) in FILTERS('Date Axis'[Quarter]) && MONTH([Date]) in FILTERS('Date Axis'[Month])
)),
"Yearly",
CALCULATE (
SUM ( 'Data'[Value] ),
FILTER ( 'Data', YEAR ( [Date] ) IN FILTERS ( 'Date Axis'[Year] )&& QUARTER([Date]) in FILTERS('Date Axis'[Quarter]) && MONTH([Date]) in FILTERS('Date Axis'[Month]) && DAY([Date]) in FILTERS('Date Axis'[Day])
)
)
)
Consider of that we need to change every measure to meet the date axis or we can let the axis table crossjoin with every possible date, we still suggest use bookmark as an effective solution.
Best regards,
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |