Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Selection between two dates

Hi there, I have a Fact table with client mutations, with eacht mutation having a StartDate and an EndDate. There is a separate Date dimension table. I am working with an SSAS Cube, Visual studio ...
  • sturlaws's avatar
    sturlaws
    6 years ago

    By VS 2017 it seems like you are refering to Visual studio, is that correct? Which version of VS you have should not matter, but you need to check the version of your SSAS installation: 

     

    to check the SSAS version go to SSMS -- connect to SSAS instance --> reports -> standard reports --> General and there would be the version for you

     

    But perhaps more important , it does not work in direct query:
    https://community.powerbi.com/t5/Desktop/SELECTEDVALUE-not-a-valid-function/td-p/270639

    you can rewrite by using the hasonevalue-function

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

When I disconnect the fact and Date dimension the BK_date filter does nothing, whether I select a single date or a range.
Now the two formulas show all clients that exist in the database.

 

Again, it does work in a local Power BI model, but not in my SSAS model. 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago
For table filter with disconnected slicer you need to do is

If(max(table[startdate])>min(dim[date]) && max(table[enddate])<min(dim[date]),1,0)

Note: i assumed after date slicer here.

And set this measure to 1 in table level filter.

Now this one will show only data which is with the above date condition.

Still if you have any doubt feel free to ask.


Thanks
Pravin wattamwar