Selection between two dates

[Anonymous]

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?

[sturlaws]

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.

[Anonymous]

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.

[sturlaws]

Here is an example of how it works: pbix

[Anonymous]

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?

[sturlaws]

Which version of SSAS are you running?

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

[Anonymous]

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 · Accepted solution]

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]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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/91...

[Anonymous]

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]

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

[Anonymous]

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

[Anonymous]

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/91...