topic Re: Drll though on positive value in DAX Commands and Tips

Drll though on positive value

joepath — Sat, 09 Apr 2022 13:16:58 GMT

I have a report TabA which is build upon the postive aggregation of TableA, now I want to drill through to TabB but only filter the postive values. and here for Amt we used dax to do some calculation. so can not just do filter positive value in the drill through TabB.

TableA

ColA	ColB	ColC	Amt
1	11	10	100
2	11	20	200
3	11	30	-400

TabA

ColB	Amt
11	300

TabB, below is the expect drill through from TabA

ColB	ColC	Amt
11	30	300

but coming like

ColB	ColC	Amt
11	60	-100

From TabA with value 11 how we can pass ColA value 1,2 also in the drill through tab to filter only positve value,

Or is there any better approch?

Thanks

Re: Drll though on positive value

Whitewater100 — Sat, 09 Apr 2022 14:03:30 GMT

Hi:

Can you try:

CALCULATETABLE (
    ADDCOLUMNS (
        SELECTCOLUMNS ( 'TableA', "ColumnB", 'TableA'[ColB,
  'Table A', "ColumnC", 'TableA'[ColC),
        "ColB Amount", SUM(TableA[ColB],
"ColC Amount", SUM(TableA[ColC]

    ),
TableA[Amt] > 0
)

Re: Drll though on positive value

joepath — Sun, 10 Apr 2022 10:48:15 GMT

So for drill through tab I need to create a calculated table? Is there any way i can create a list of positive values of ColA that can always be default filter in drill through tab, and those value can change by changing the date filter.

TableA is build up some other table so these positive negative amt can change by changing the date filter.

Re: Drll though on positive value

v-yalanwu-msft — Tue, 12 Apr 2022 02:33:30 GMT

Hi, joepath ;

I am sorry that I cannot reproduce the example you gave. What is the data of your table A and table B, and what visual is used to show the result? Can you share a simple file if possible?
Or you can refer to the documentation and video below.

https://www.youtube.com/watch?v=zeH1Hq2koUU

https://visualbi.com/blogs/microsoft/powerbi/conditionally-drill-action-power-bi/

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Drll though on positive value

joepath — Tue, 12 Apr 2022 08:01:22 GMT

Hi, v-yalanwu-msft;

To simplify the problem, below is the matric report. now when I drill through from open to drill through tab then all the value under open should pass to drill through tab as a filter.

Under the open we can also have negative value that I have filter out in the filter pan, now only visible values under open should pass to drill through tab. by default all the value positive or negative are passing.

Thanks,