measures drill through
1 Topic
Creating a Card, with a drill-through, based on a count of a measure.
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.Solved471Views2likes1Comment