Forum Discussion

Anderrated's avatar
Anderrated
Frequent Visitor
3 years ago
Solved

Create new table using dynamic measure to filter

So, I am fairly new to PowerBI, i tried to understand similar topics to my issue but i can't make it work. My case is the following:

 

I have TableA which can be summarized down to two columns:

 

TableA --> named labelpouch

Date, Lot

 

This Table A is filtered by a date range Slicer.

 

From this Slicer i compute two measures that correspond to the selected values by the user in the slicer and that are updated when the slicer is changed in report view

 

MinSlicerDate = min(labelpouch[Date])
MaxSlicerDate = max(labelpouch[Date])

 

 

 

What i want to do is to create a new table B which shows the LOTs that have a Date>MaxSlicerDate, for some reason this formula is not working:

 

FilteredTable = SELECTCOLUMNS(FILTER(labelpouch,labelpouch[Date]>MaxSlicerDate),"LOT",[LOT],"Date",[Date])

 
I have tried also with function FILTER directly with bad results. What am i missing here? 
 
Thanks a lot!

 

Need Help
Tips and Tricks
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, Anderrated 

    Thanks for your quick response and provided table . Yes, you want to use the slicer's date into the calculated table, it will not work.

    For your need , you need to first to create a Date table as a slicer, like this:

    Date = CALENDAR(FIRSTDATE('TableA'[Date]),LASTDATE('TableA'[Date]))

     

    Then you need to make the relationship like this:

    Now, we can use the 'Date'[Date] as a slicer to filter 'TableB'  and we can use the 'TableA' as 'TableC' you want to get . 

     

    We can create a measure:

    Measure = var _slicer_date = MAX('Date'[Date])

    var _cur_date = MAX('TableA'[Date])

    return

    IF(_cur_date> _slicer_date ,1,0)

     

    Then we can put the measure on the visual (‘TableA’),like this:

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anderrated 

    The "var _cur_date = MAX('TableA'[Date])" return the Date every row in Matrix .You can create a measure to test like this:[The measure is calcualteing in the current context filter!]
    Measure= MAX('TableA'[Date])

     

    And the "MAX('Date'[Date])" , your understand is right~

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

     

5 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , Anderrated 

    Accorsing to your description, you have a date slicer in the visual , and you want to use this slicer to create a calcualted column?

    For your needs, there is no way to achieve this. The row context used by the calculated table in Power BI Desktop is a filter context for the slicer, and we have no way to get the selection value of the corresponding slicer when creating the calculated table.

    For slicers, we generally use them in measure or on visuals.

    You can use measure to return the corresponding value, and then place measure in the visual "Filter on this visual" to implement the need to filter the table according to your custom needs.

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

    • Anderrated's avatar
      Anderrated
      Frequent Visitor
      3 years ago

      Hi, 

       

      Sorry it did not work, let me add details to my issue:

       

      My original table of data is the following:

      TableA

      LotDate
      A01/01/2022
      B10/06/2022
      C01/12/2022

       

      I create a second table: 

      TableB = 'TableA', the date of this second table is referenced to the slicer.

      In the slicer i select for example date range from 01/06/2022 to 31/06/2022.

       

      TableB now looks like:

      LotDate
      B10/06/2022

       

      I have two measures that i want them to select the two date values of the slicer:

       

      MinValueSlicer = min(TableB[Date])

      MaxValueSlicer = max(TableB[Date])

      Is this correct???

       

      Now i have a third table, TableC. I want Table C to show the rows of TableA that have Date > MaxDateSlicer

      Like this:

      LotDate
      C01/12/2022

      For this i create:

      TableC= SELECTCOLUMNS(FILTER('TableA','TableA'[Date]>MaxSlicerDate),"LOT",[LOT],"Date",[Date])

       

      For some reason this is not working. Thanks for your time

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi, Anderrated 

        Thanks for your quick response and provided table . Yes, you want to use the slicer's date into the calculated table, it will not work.

        For your need , you need to first to create a Date table as a slicer, like this:

        Date = CALENDAR(FIRSTDATE('TableA'[Date]),LASTDATE('TableA'[Date]))

         

        Then you need to make the relationship like this:

        Now, we can use the 'Date'[Date] as a slicer to filter 'TableB'  and we can use the 'TableA' as 'TableC' you want to get . 

         

        We can create a measure:

        Measure = var _slicer_date = MAX('Date'[Date])

        var _cur_date = MAX('TableA'[Date])

        return

        IF(_cur_date> _slicer_date ,1,0)

         

        Then we can put the measure on the visual (‘TableA’),like this:

        Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

         

        Best Regards,

        Aniya Zhang

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

  • Anderrated's avatar
    Anderrated
    Frequent Visitor
    3 years ago

    Hi Aniya, it worked!!!, that was very clever solution! I have one more question, it is working but i would like to understand.

     

    Measure = var _slicer_date = MAX('Date'[Date]) ----> the maximum of Date i understand is the slicer selected max date, in your example is 30/06/2022

     

    var _cur_date = MAX('TableA'[Date]) --> the maximum value of TableA, which is 01/12/2022

    return

    IF(_cur_date> _slicer_date ,1,0)

     

    Then the IF statement is IF(01/12/2022>30/06/2022) return a 1 if not return a 0. But isn't it always true? what i see it happens is that it asigns a 0 on the dates < than 30/06/2022 and a 1 to the dates > 30/06/2022. but i dont understand why. Shouldn't it be always 1?¿

    • v-yueyunzh-msft's avatar
      v-yueyunzh-msft
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi , Anderrated 

      The "var _cur_date = MAX('TableA'[Date])" return the Date every row in Matrix .You can create a measure to test like this:[The measure is calcualteing in the current context filter!]
      Measure= MAX('TableA'[Date])

       

      And the "MAX('Date'[Date])" , your understand is right~

       

      Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

       

      Best Regards,

      Aniya Zhang

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