Drill through by different columns

Is it possible to drillthrough on mutliple columns in a table to obtain different results?

I need to see the invoices that make the $$. When I drillthough on any column, I get all the invoices, but I only want to see the filtered list.

For example: Considering Vendor ABC:

When I drill on Total Invoice Count - I want to see all 4 invoices. But when I drill on 60 Days, I only want to see the 3 invoices.

Currently, I can click on any cell, and I see all 4 invoices.

Vendor	Total Invoice Count	Total Amount	Invoice count - Due in next 30 Days	30 Days	Invoice count - Due in next 60 Days	60 Days
ABC	4	$30	1	$10	3	$20
DEF	3	$40	1	$15	2	$25
GHI	2	$20	2	$20	0	$0

Best

Seema

Need Help

v-lid-msft

6 years ago

Hi Anonymous ,

We can use following workaround to meet your requirement:

1. create a column header table by following calculated column formula (column name is sorted by the sort column) :

ColumnHeader = DATATABLE("ColumnName",STRING,"Sort",INTEGER,
{{"Total Invoice Count",1},
{"Total Amount",2},
{"Invoice count - Due in next 30 Days",3},
{"Invoice Amount - Due in next 30 Days",4},
{"Invoice count - Due in next 60 Days",5},
{"Invoice Amount - Due in next 60 Days",6}}
)

2. create a measure and use it as value field of matrix visual, put the column name in column field of matrix.

Measure:

Matrix Value = SWITCH(SELECTEDVALUE('ColumnHeader'[ColumnName],BLANK()),"Total Invoice Count",[Total Invoice Count] & "",
"Total Amount",[Total Amount] & "",
"Invoice count - Due in next 30 Days",[Invoice count - Due in next 30 Days] & "",
"Invoice Amount - Due in next 30 Days",FORMAT([Invoice Amount - Due in next 30 Days],"Currency"),
"Invoice count - Due in next 60 Days",[Invoice count - Due in next 60 Days] & "",
"Invoice Amount - Due in next 60 Days",FORMAT([Invoice Amount - Due in next 60 Days],"Currency")
,BLANK())

our sub measures are following, you can use your own measures:

Total Invoice Count = DISTINCTCOUNT('Table'[Invoice ID])

Total Amount = SUM('Table'[Invoice Amount])

Invoice count - Due in next 30 Days = CALCULATE(DISTINCTCOUNT('Table'[Invoice ID]),FILTER('Table',DATEDIFF(TODAY(),'Table'[Due Date],DAY)<=30))

Invoice Amount - Due in next 30 Days = CALCULATE(SUM('Table'[Invoice Amount]),FILTER('Table',DATEDIFF(TODAY(),'Table'[Due Date],DAY)<=30))

Invoice count - Due in next 60 Days = CALCULATE(DISTINCTCOUNT('Table'[Invoice ID]),FILTER('Table',DATEDIFF(TODAY(),'Table'[Due Date],DAY)<=60 && DATEDIFF(TODAY(),'Table'[Due Date],DAY)>30))+0

Invoice Amount - Due in next 60 Days = CALCULATE(SUM('Table'[Invoice Amount]),FILTER('Table',DATEDIFF(TODAY(),'Table'[Due Date],DAY)<=60 && DATEDIFF(TODAY(),'Table'[Due Date],DAY)>30))+0

3. create a measure and put into the visual filter of visual in drill-through page, set condition "is 1",

Measure:

Visual Control = SWITCH(SELECTEDVALUE(ColumnHeader[ColumnName],BLANK()),"Invoice count - Due in next 30 Days",IF([Invoice count - Due in next 30 Days]>0,1,-1),"Invoice count - Due in next 60 Days",IF([Invoice count - Due in next 60 Days]>0,1,-1),1)

4. put the vender column and Column Name column into drill-through field of page