Forum Discussion

qbarnes's avatar
qbarnes
Helper I
6 years ago
Solved

Disconnected Table for Date Hierarchy

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.    

 

  • NOTE: I would normally use the drilldown function, but I'm being asked to create a very user-friendly report, its visibility will be for users that aren't too familiar with PBI ---  and I want to avoid bookmarks, as I have several pages to update.  

 

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??

 

Tips and Tricks
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

1 Reply

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,