Forum Discussion
Using a measure to filter a new calculatedtable
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
3 Replies
- v-eachen-msft
Community Support
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 )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.
