Forum Discussion
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 and a live connected Db.
In the report, I want to be able to show the number of clients on a selected date.
So I first created a measure that gives me a value when the selected date is in between the StartDate and EndDate:
CheckDate:= VAR SELECTEDDATE = SELECTEDVALUE(Dim_REF_Date[BK_Date])
RETURN
IF(SELECTEDDATE > MIN(FCT_JGZ[StartDate]) && SELECTEDDATE < MAX(FCT_JGZ[EndDate]); 1; 0)
Then I created a measure to count the number of clients:
ClientsOnDate:= CALCULATE(DISTINCTCOUNT('Fct_JGZ'[ClientNumber]); 'Fct_JGZ'; [CheckDate]=1)
This gave an error: 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
So I changed this to:
ClientsOnDate:= CALCULATE(DISTINCTCOUNT('Fct_JGZ'[ClientNumber]); FILTER('Fct_JGZ'; [CheckDate]=1))
Although this formula does not show an error.. in the report, this measure doesn't show anything..?
Am I doing something wrong?
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
15 Replies
Hi Anonymous
try this for your measure:
ClientsOnDate := VAR SELECTEDDATE = SELECTEDVALUE ( Dim_REF_Date[BK_Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Fct_JGZ'[ClientNumber] ); FILTER ( 'Fct_JGZ'; SELECTEDDATE > FCT_JGZ[StartDate] && SELECTEDDATE < FCT_JGZ[EndDate] ) )Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.Hi Anonymous
ClientsOnDate:= CALCULATE(DISTINCTCOUNT('Fct_JGZ'[ClientNumber]),filter( 'Dim_REF_Date',Dim_REF_Date[BK_Date]>MIN(FCT_JGZ[StartDate]) && Dim_REF_Date[BK_Date]< MAX(FCT_JGZ[EndDate]))\
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/913784Hi Anonymous
Thank you for your reply. The formula 'sort of' works, but it only shows the clients who have the exact BK_date that I select.
I then realized I have another problem:
The Date Dimension is connected to the Fact StartDate column (via DateID columns).
So when I use BK_Date in a filter, it logically filters the Fact for only those StartDate's.
I tried to set the connection to 'unactive', but this results in no data showing at all.
I'm not sure how to solve this.
In addition, when I make a copy of my model in Power Bi Desktop (instead of in SSAS), the formula does show the correct result, as long as I set the connection to 'unactive'. Any idea why it does work here and not in my tabular model?M.Lameijer
Hi sturlaws
Thank you for your reply.
However, the formula does not seem to work. In my report, it just says 'can't display the visual'.
It can't seem to resolve the BK_date as Selectedvalue.