topic Re: Selection between two dates in DAX Commands and Tips

Selection between two dates

Anonymous — Thu, 30 Jan 2020 12:18:15 GMT

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?

Re: Selection between two dates

sturlaws — Mon, 03 Feb 2020 08:56:14 GMT

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.

Re: Selection between two dates

Anonymous — Mon, 03 Feb 2020 10:11:05 GMT

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

Re: Selection between two dates

Anonymous — Mon, 03 Feb 2020 13:51:47 GMT

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

Re: Selection between two dates

Anonymous — Mon, 03 Feb 2020 14:03:12 GMT

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/913784

Re: Selection between two dates

Anonymous — Mon, 03 Feb 2020 14:51:07 GMT

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.

Re: Selection between two dates

Anonymous — Mon, 03 Feb 2020 15:05:11 GMT

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.

Re: Selection between two dates

Anonymous — Mon, 03 Feb 2020 17:39:05 GMT

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

Re: Selection between two dates

sturlaws — Mon, 03 Feb 2020 19:50:13 GMT

Here is an example of how it works: pbix

Re: Selection between two dates

Anonymous — Tue, 04 Feb 2020 10:55:21 GMT

I am sorry, but I don't understand.

First, what is a table level filter?

I created the IF-statement as a measure, but it cant be added to a slicer or used as a visual level filter.

Going back to my measure that uses selectedvalue, the error it gives is:
Failed to resolve name 'SELECTEDVALUE'. It is not a valid table, variable or function name.

Does this give you an idea of why it is not working? (I am working in January 2019 version of PBI for Reporting Server.)
Is selectedvalue even supported in live connection/direct query?

Thanks

Re: Selection between two dates

Anonymous — Tue, 04 Feb 2020 10:57:54 GMT

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?

Re: Selection between two dates

Anonymous — Tue, 04 Feb 2020 11:01:47 GMT

sorry tablel level filter means visual level filter.

follow the steps which i have suggested.

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

Re: Selection between two dates

sturlaws — Tue, 04 Feb 2020 11:02:30 GMT

Which version of SSAS are you running?

According to this: https://dax.guide/selectedvalue/ selectedvalue is supported in SSAS 2017 and 2019

Re: Selection between two dates

Anonymous — Tue, 04 Feb 2020 11:40:39 GMT

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

Re: Selection between two dates

sturlaws — Tue, 04 Feb 2020 11:59:48 GMT

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

Re: Selection between two dates

Anonymous — Tue, 04 Feb 2020 15:43:17 GMT

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.