Help with slicer

[kdendukuri]

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

[v-chenwuz-msft]

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.

[amitchandak]

@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-tr...

[kdendukuri]

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