Create new table using dynamic measure to filter

[Anderrated]

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!

[v-yueyunzh-msft · Accepted solution]

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]

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 · Accepted solution]

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]

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]

Hi, 

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

My original table of data is the following:

TableA

Lot	Date
A	01/01/2022
B	10/06/2022
C	01/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:

Lot	Date
B	10/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:

Lot	Date
C	01/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 · Accepted solution]

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