Forum Discussion
Selection between two dates
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
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/913784
Hi 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
In SSAS Model only disconnect date dimension with your fact.
I assume your fact table have start date and end date.
Now come to power Bi and use date dimension is slicer as after/before/Between slicer as per requirement.
I am giving example for after slicer.
Calculate(count(Fact[column]),filter(fact,fact[star_date] < min(dim[Date]) && fact[end_date] > min(dim[Date]))
Just check those less than greater than conditions as per your requirement.
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/913784When 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.
- 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