Forum Discussion

Jack09's avatar
Jack09
Frequent Visitor
8 years ago

How to use slicer value in different Table Measure

Hello,

 

I have to Tables which are RecurringDetails and other is Date Dimension.

 

I have two Slicer from Date Dimension and it is Year and Month.

 

Now, I want Both Slicer value in Measure of RecurringDetails table. So can you please help to achieve this.

 

NOTE: There is no relationship between these two tables.

 

Sample DAX:

Static Current Month Count=
       CALCULATE(COUNT('Dimension Recent_RecurringGiving'[PK_Id]),
            FILTER('Dimension Recent_RecurringGiving',
            'Dimension Recent_RecurringGiving'[EndDateKey] <> BLANK()
             && 'Dimension Recent_RecurringGiving'[EndDateKey] >= 20160701
             && 'Dimension Recent_RecurringGiving'[EndDateKey] <= 20170331)
)

 

Above Date, numbers should be based on Month Selected in Slicer

 

 

Thanks in Advance,

Jaymin Patel

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi Jack09

    First, i think your formula need to be modified as below

    Static Current Month Count = 
       CALCULATE(COUNT('Dimension Recent_RecurringGiving'[PK_Id]),
            FILTER(ALL('Dimension Recent_RecurringGiving'),
            'Dimension Recent_RecurringGiving'[EndDateKey] <> BLANK()
             && 'Dimension Recent_RecurringGiving'[EndDateKey] >= DATE(2016,7,1)
             && 'Dimension Recent_RecurringGiving'[EndDateKey] <= DATE(2017,3,31))
)

    If you want the Slicer from Date Dimension to apply in Measure of RecurringDetails table, as i understand, it should like this, replace the date

    Static Current Month Count = 
       CALCULATE(COUNT('Dimension Recent_RecurringGiving'[PK_Id]),
            FILTER(ALL('Dimension Recent_RecurringGiving'),
            'Dimension Recent_RecurringGiving'[EndDateKey] <> BLANK()
             && 'Dimension Recent_RecurringGiving'[EndDateKey] >= [min]
             && 'Dimension Recent_RecurringGiving'[EndDateKey] <= [max])
)
    max = CALCULATE(MAX('Date Dimension'[date]),ALLSELECTED('Date Dimension'[date]))
    min = CALCULATE(MIN('Date Dimension'[date]),ALLSELECTED('Date Dimension'[date]))

    Before above, you need edit relationship between two tables

     

    Best Regards

    Maggie

    • Jack09's avatar
      Jack09
      Frequent Visitor
      8 years ago

      Hi,

       

      Thanks for your efforts but somehow it is not as per my requirement

       

      I think I was not able to explain to you my exact requirement.

       

      Then after, I am using Year and Month Separately in Slicer so there might not be Min and Max options.

       

      Now, comes to exact requirements are as followed:

      • When I will select March-2017 then based on this month what i want is as followed:
        • Financial Year Start Date(1st July 2016)
        • End of Month Date(31st March 2017)
      • I have two table and there is not even a Single column for Date which we can relate.
      • I just need to Compare above dates from EndDate of Recurring Giving table.

       

      So the final outcome for the requirement is How to get selected value in Slicer when any column is not related in two table.

       

      Thanks in advance

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi Jack09

        I'm afraid it is not possible to get selected value in Slicer when any column is not related in two table, i recommend you use some dax function to see if helps.

        DATEADD can shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

        This can get 2016/6/1 from 2017/3/1.

        ENDOFMONTH returns the last date of the month in the current context for the specified column of dates. 

        This will get 2017/3/31 for 2017/3/1.

         

        Best Regards

        Maggie