Using a measure to filter a new calculatedtable

[Anonymous]

Hi community,

As a rather new Power BI user I am struggling with a probably simple problem:

I am trying to use Min and Max dates from my dateslicer to create a new calculatetable.

I have managed to “store” the Min and Max dates in two measures using the following syntax:

MaxDate = DATEVALUE(CALCULATE(MAX('Calendar'[Date]), ALLSELECTED('Calendar'[Date])))

This correctly picks the dates from my slicer.

Now I want to use this to create a new table:

Active users =
CALCULATETABLE(’MyMainTable',
FILTER(MyMainTable ',
MyMainTable)'[SignUpDate] < [MinDate] &&
MyMainTable)'[ChurnDate] > [MaxDate]
)
)

But when I do this the table comes up empty. If I replace the measures with DATE(2019,11,1) and DATE(2019,11,30) the tables is filled correctly.

I have made a check with a new measure:

IsMaxDateCorrect = IF([MaxDate] = Date(2019,11,30), TRUE(),FALSE())

Which returns true.

Therefore I am baffled – why is the measure not working as the filter when it contains the same data as DATE() and DATE() works in the syntax?

I might be doing the whole thing pretty backasswards but please be gentle, I am a newbie. (I might not have a good explanation to why I am going this way around the trunk as I have made this with 3-5 different forum threads to make various aspects of the setup)

Kind regards,

Anders

[v-eachen-msft]

Hi @Anonymous ,

I think that you want to create a dynamic table based on selected values on the slicer.

However, you will get a static table if you used CALCULATETABLE() function. The range of [MinDate] and [MaxDate] measures will always be the entire table.

If you want to filter the orginal table, you could create measure to set the data that should be displayed ( for example, the matching data is displayed as 1 and the others are 0 ). Then set rules in the filter pane to show right values.

Active users =
IF(
SELECTEDVALUE('MyMainTable'[SignUpDate]) < [MinDate] &&
SELECTEDVALUE('MyMainTable'[ChurnDate]) > [MaxDate],
1,
0
)

[Anonymous]

Hi @v-eachen-msft ,

Thank you for your reply.

I have now made the measure that you wrote. However, when I make a table visualisation with all my unique ID's every single row is marked with a 0, so somehow the the the MinDate and MaxDate is proving errornous in the comparrison.

[Anonymous · Accepted solution]

I ended up using a Hirearchy slicer from the Marketplace instead.

This accompanied by a calendar table seemed to be the most robust solution.