Forum Discussion

kdendukuri's avatar
kdendukuri
New Member
4 years ago

Help with slicer

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

    • kdendukuri's avatar
      kdendukuri
      New Member
      4 years ago

      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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.