topic Re: Help with slicer in DAX Commands and Tips

Help with slicer

kdendukuri — Fri, 13 May 2022 02:27:24 GMT

I have Customer key, Officekey and Datetrx and DatePurge, Amount. I need my report with the below conditions

1. Datepurge is null or Datepurge >= VariableDate (a Date that user selects)

2. Datetrx <= VariableDate (a Date that user selects)

I tried using slicers on both columns and give bot the same value, but i couldnt accomodate the or condition with Datepurge NULL or >= VariableDate (a Date that user selects)

Tried with below dax measure but when enabled with slicers it also filters customer key and office key data due to slicers effecting the visual

cal_mga =

VAR seldatepurge = SELECTEDVALUE ( MGA_AROPEN[DATEPURGE] )

VAR seldatetrx = SELECTEDVALUE ( MGA_AROPEN[DATETRX] )

VAR FilteredTable =

CALCULATETABLE (

MGA_AROPEN,

MGA_AROPEN[CONO]=10 && MGA_AROPEN[COVTYPE]="ComTitle" && MGA_AROPEN[DATETRX] <= seldatetrx &&

OR(ISBLANK(MGA_AROPEN[DATEPURGE]), MGA_AROPEN[DATEPURGE]>= seldatepurge))

VAR Result = SUMX ( FilteredTable, MGA_AROPEN[AMOUNTNET] )

RETURN Result

Re: Help with slicer

amitchandak — Fri, 13 May 2022 05:59:58 GMT

kdendukuri , With a small changes

VAR FilteredTable =
CALCULATETABLE (
MGA_AROPEN,Filter(MGA_AROPEN,
MGA_AROPEN[CONO]=10 && MGA_AROPEN[COVTYPE]="ComTitle" && MGA_AROPEN[DATETRX] <= seldatetrx &&
OR(ISBLANK(MGA_AROPEN[DATEPURGE]), MGA_AROPEN[DATEPURGE]>= seldatepurge)))

VAR Result = SUMX ( FilteredTable, MGA_AROPEN[AMOUNTNET] )

Also, refer approach in

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: Help with slicer

kdendukuri — Fri, 13 May 2022 11:48:25 GMT

I dont have issue with the my dax formula. It is working as expected when i dont have slicers Datepurge and Datetrx in my report. WHen i have those slicers and i chose same value for both which is my varibaledate , i have my other dimesnison values getting as these slicers getting applied and i cant enforce datepurge to have "is null" or >= Variable Date only >=varibale date can be done using slicer

Re: Help with slicer

v-chenwuz-msft — Tue, 17 May 2022 08:09:58 GMT

Hi kdendukuri ,

You need create a new table via values(table[Datepurge]) for the slicer just giving Variable to the measure and not participating in the filter context. The new table should have no relationships with this fact table. It is much better to get variables from the slicer via max() or min() than selectedvalue() to avoid blank.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.