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

Thanks a lot, I had also tried to create my model in pbi deskstop. But again, the formula works in PBI desktop, but not in my live connected SSAS model.

Is SELECTEDVALUE supported for live connections? And if not, are there other ways to do this?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I am using VS 2017 - Version 15.8.7
    I am also using Power BI for reporting service, version Jan 2019.
    I am using SSMS 13.0.5101.9 

    • sturlaws's avatar
      sturlaws
      Icon for Resident Rockstar rankResident Rockstar
      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

        Thanks, I will try to update my ssas in due time.

        For now I made the following measure, which seems to work:
        ClientsonDate:=IF ( HASONEVALUE ( 'Date'[BK_Date] );
        CALCULATE ( DISTINCTCOUNT ( 'Fct'[clients] );
        FILTER ( 'Fct'; 'Fct'[Startdate] <= MIN ( 'Date'[BK_Date] ) && 'Fct'[EndDate] >= MAX ( 'Date'[BK_Date] )))

         

        But this measure also works fine without the IF (HASONEVALUE() part. So I was overthinking the whole thing and just needed to deactivate the connection between the Date and Fact tables.