Creating a Card, with a drill-through, based on a count of a measure.

[DomDom78]

Hello All, 

I am setting up a IT hardware dashboard. I have a weekly report (I've added a Date Index column) from our hardware tool, showing basic hardware devices information, plus device status and departmental information. My main aim of this dashoard is to identify changes over time e.g movement between departments, changes to the status of devices. I am using the field "BIOS Serial Number' as the device unique identifier. My thought were to create a dashboard page with:

Relative Date Slicer

A Card to show status changes (based on certain criteria) 

A Drill though to the data

Based on Department, my approach so far is to create measures to identify the Departments on min and max 'Date Index' E.g:

MaxDateDepartment = 
VAR MaxDateIndex = 
    CALCULATE(
        MAX('Report'[Date Index]), 
        ALLSELECTED('Report')
    )
RETURN
CALCULATE(
    FIRSTNONBLANK('Report'[Department], 1),
    'Report'[BIOS Serial Number] = SELECTEDVALUE('Report'[BIOS Serial Number]) &&
    'Report'[Date Index] = MaxDateIndex
)

MinDateDepartment = 
VAR MinDateIndex = 
    CALCULATE(
        MIN('Report'[Date Index]), 
        ALLSELECTED('Report')
    )
RETURN
CALCULATE(
    FIRSTNONBLANK('Report'[Department], 1),
    'Report'[BIOS Serial Number] = SELECTEDVALUE('Report'[BIOS Serial Number]) &&
    'Report'[Date Index] = MinDateIndex
)

I then have a measure to compare the two values and provide a status. 

TransferStatusMeasure = 
VAR MinD = [MinDateDepartment]   -- Get the Department at MinDate
VAR MaxD = [MaxDatePDepartment]   -- Get the Department at MaxDate

RETURN
    IF (
        ISBLANK(MaxD) && NOT(ISBLANK(MinD)), 
        "Transferred Out",  -- Device is missing in MaxDate but existed in MinDate      
            IF (
                ISBLANK(MinD), 
                "Transferred In",  -- Device is new in MaxDate
                IF (
                    MinD = MaxD, 
                    "No Change",  -- Department unchanged
                    "Transferred In"  -- Department changed (still treated as Transferred In)
                )
            )
        )

I've tested this with the date slicer, and the Departments and Statuses dynamically change, based on the slicer. My next step is to add a Card, to count the different statuses. I think this is where I may have taken the wrong route. I have created more measures to count the statuses. e.g. 

CountTransferredIn = 
CALCULATE(
    COUNTROWS(
        FILTER(
            DISTINCT(VALUES('Report'[BIOS Serial Number])),
            [TransferStatusMeasure] = "Transferred In"
        )
    ),
    ALLSELECTED('Report'[Date Index]),
    VALUES('Report'[BIOS Serial Number])
)

I have then added this measure to a card. I have added the same measure to my drill though data page as a Drill Through, as well as a column in the table. This is where i get stuck. The drill through page doesn't filter. I've tried to remove non-filterable columns, but I can't filter to show the relevant devices.

I've tried using calculated columns, but the card value doesn't update based on the slicer. Any advice would be greatly appreciated. I'm comfortable with the min, max and compare measures. (although i may have made mistakes). It's the card and the drill-through where I am stuck. 

[Sahir_Maharaj · Accepted solution]

Hello @DomDom78,

Can you please try the following approach:

1. Add a Calculated Column for Drill-Through

TransferStatusColumn = 
VAR MinD = CALCULATE(
    FIRSTNONBLANK('Report'[Department], 1),
    'Report'[Date Index] = CALCULATE(MIN('Report'[Date Index]))
)
VAR MaxD = CALCULATE(
    FIRSTNONBLANK('Report'[Department], 1),
    'Report'[Date Index] = CALCULATE(MAX('Report'[Date Index]))
)
RETURN
    IF (
        ISBLANK(MaxD) && NOT(ISBLANK(MinD)),
        "Transferred Out",
        IF (
            ISBLANK(MinD),
            "Transferred In",
            IF (
                MinD = MaxD,
                "No Change",
                "Transferred In"
            )
        )
    )

2. Update the Measure for the Card

CountTransferredIn = 
CALCULATE(
    DISTINCTCOUNT('Report'[BIOS Serial Number]),
    'Report'[TransferStatusColumn] = "Transferred In"
)

Hope this helps.