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:   ...
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

     

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