Drill through by different columns

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

By the way, PBIX file as attached.

Best regards,