cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qbarnes
Regular Visitor

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

Sample.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Disconnected Table for Date Hierarchy

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])
         )
    )
)

 

6.jpg7.jpg

 

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,

 

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.

View solution in original post

1 REPLY 1
Community Support
Community Support

Re: Disconnected Table for Date Hierarchy

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])
         )
    )
)

 

6.jpg7.jpg

 

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,

 

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.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors