Using a Custom Table to Tidy Things Up - Filter by Date

[patri0t82]

Hello, I'm building a complex report that contains many, many measures. I was going to display all these measures in little boxes, and try to align the boxes to make it look aesthetically pleasing. This is proving to be quite difficult. I would like to instead place all my measures in a table and then use a formatted table visualization to display the results.

The problem is, when I build the table, my date slicers have no effect on the results. The measures individually work correctly, so I'm hoping to find a way to tie it to the calendar. Here is the custom Table formula (so far). I've included a couple images to show how the report currently looks also.

The bottom line, I guess, is there a better option for me, or any way to take into account the CalendarTable?

Table = 
VAR _MEASURENAME = { "LTIR", "LTIR Incidents", "RIF", "RIF Incidents" }
VAR _ADDCOLUMN =
    ADDCOLUMNS (
        _MEASURENAME,
        "This Month",
            SWITCH (
                [Value],
                "LTIR", [HSE - TM LTIR],
                "LTIR Incidents", [HSE - TM Lost Time Count],
                "RIF", [HSE - TM RIF],
                "RIF Incidents", [HSE - TM Recordable Injury Count]
            ),
        "This Quarter",
            SWITCH (
                [Value],
                "LTIR", 'Calculations - HSE'[HSE - TQ LTIR],
                "LTIR Incidents", [HSE - TQ Lost Time Count],
                "RIF", [HSE - TQ RIF],
                "RIF Incidents", [HSE - TQ Recordable Injury Count]
            )            
    )
RETURN
    _ADDCOLUMN

[DataInsights · Accepted solution]

@patri0t82,

I would create a calculation group with two calculation items: This Month, and This Quarter. The concept is to define base measures and then each calculation item will calculate the measure in the specified context (this month, this quarter, etc.) This will reduce the number of measures in your model. See the article below for details.

https://www.sqlbi.com/articles/introducing-calculation-groups/ 

You can create a metric table with one row per metric, and (optionally) a sort column so you can control the sorting in a visual. Use this metric table in your visual. Then, create a measure that uses SELECTEDVALUE to get the metric for each visual row, and return the corresponding measure.

[patri0t82]

Thank you for the reply - I'm sorry for the delay in responding. I understand (I think) what you're getting at, and appreciate you providing the link. Do I need to install or utilize a third party application like Tabular Editor, or is this just a change in thinking in the way I build measures?

[DataInsights]

@patri0t82,

You need to install Tabular Editor to create calculation groups. There's a free version (2.x) and a paid version. Once you install it, open your pbix, go to External Tools, and select Tabular Editor. Here's the download link:

https://tabulareditor.com/downloads 

Let me know how it goes.

[patri0t82]

Thank you again. I've installed Tablular Editor and created a few Time Intelligence measures, per the link you provided. I can definitely see some benefits from calculated groups. I'm still, however having difficulty, and maybe you can tell me where I'm going wrong. 

I created measures for the time periods I would like to see (YTD, YTD-LY, TM, TM-LY, QTD, QTD-LY)

I've also created a simple measure to count Lost Time injuries:

HSE - Lost Time Count = 
CALCULATE (
    COUNT ( 'Recordable Injuries and Events'[Recordable Injury] ),
    'Recordable Injuries and Events'[Recordable Injury] = "Lost Time",
    'Recordable Injuries and Events'[Recordable Injury] = "Fatality"
)

When I place my Time Intelligence metrics in the column of a matrix, I see this:

I am unable to use my Lost Time count for the Rows. (in my previous posts, this would be LTIR Incidents).

Simply, it seems like my Lost Time measure is not tied to the Calendar table, or associated with my Time Intelligence measures.

[DataInsights]

@patri0t82,

Does the Calendar table have a relationship with the Recordable Injuries and Events table? This is necessary for date calculations. Be sure to mark the Calendar table as a date table.

[patri0t82]

Thanks for the response. CalendarTable was not in fact set as the Date table. I did change it though, and yet I haven't seen a change. I did try changing the direction to both, as well, with no luck.

[DataInsights]

@patri0t82,

Do you have a date slicer or filter for your visual?

[patri0t82]

Before I made the Mark as Calendar Table change, I was using a separate slicer for Month Name and another for Year. Will those no longer work if I mark as Calendar Table?

[DataInsights]

Marking the Calendar table as a date table won't affect the slicers. Try using a slicer for Date instead and see if that works. Can you share your sanitized pbix via one of the file services like OneDrive?

[patri0t82]

Hi, I'm afraid even wide open with no date selected it's showing all zeros. I understand if there's not much more you can do without seeing something. I appreciate you getting me this far!

[DataInsights]

@patri0t82,

Just to confirm, do you have the Lost Time measure in the Values field well of the matrix? Since your YTD calculation item ends with "+ 0", it will display 0 if the result is blank.

[patri0t82]

Hi, yes that's correct. I'm sorry this has been confusing. I've tried to capture everything in one screenshot for you.

[DataInsights]

@patri0t82,

I think the issue is with your measure. Filter arguments are combined (intersected), so you need to write it like this if you want to use OR instead of AND:

HSE - Lost Time Count =
CALCULATE (
    COUNT ( 'Recordable Injuries and Events'[Recordable Injury] ),
    'Recordable Injuries and Events'[Recordable Injury]
        IN { "Lost Time", "Fatality" }
)

[patri0t82]

Hey, that worked! The numbers are appearing properly now. 

With that said, back to my original post, I had may rows, 

LTIR

LTIR Incidents

RIF

RIF Incidents

etc.

If I use my HSE - Lost Time Count measure in the values field the numbers appear correctly, however I only want the first (second) row to show as LTIR Incidents. Any idea what I would need to do to populate the Rows?

[DataInsights]

Excellent! You can add all measures to the Values field well, and enable the setting "Switch values to rows":

[patri0t82]

You sir, have gone above and beyond. Thank you again.