Combine calculatetable with selectedvalue on date ...

Murat62 · ‎09-04-2018

Hi every body,

I am struggling with the following issue.

I have a simple fact table on sales per customer_id & date

I build 2 tables from fact_Sales to get all dates

D1 date = SUMMARIZE('Fact Sales';[Date])

D2 date = SUMMARIZE('Fact Sales';[Date])

I used them as slicers to select manually 2 dates

Now my purpose is to identify Customer_id which are common (having sales at those 2 dates)

So, I wrote this measure

Common customers =
VAR D1Items = CALCULATETABLE(VALUES('Fact Sales'[Customer_Id]);'Fact Sales'[Date]=SELECTEDVALUE('D1 date'[D1 Dates];DATE(2018;05;01)))
VAR D2Items = CALCULATETABLE(VALUES('Fact Sales'[Customer_Id]);'Fact Sales'[Date]=SELECTEDVALUE('D2 date'[D2 Dates];DATE(2018;07;01)))
RETURN
INTERSECT(D1Items;D2Items)

And i am getting an erreur where selectedvalue is used in true & false expression which is not authorized...

Any ideas or solutions from you will be appreciated & thanks in advance

Regards

v-yuta-msft · ‎09-05-2018

Hi Murat62,

Modify your measure as below and check if it can meet your requirement.

Common customers =
VAR D1Items =
    CALCULATETABLE (
        VALUES ( 'Fact Sales'[Customer_Id] );
        FILTER (
            'Fact Sales';
            'Fact Sales'[Date] = SELECTEDVALUE ( 'D1 date'[D1 Dates] )
        )
    )
VAR D2Items =
    CALCULATETABLE (
        VALUES ( 'Fact Sales'[Customer_Id] );
        FILTER (
            'Fact Sales';
            'Fact Sales'[Date] = SELECTEDVALUE ( 'D2 date'[D2 Dates] )
        )
    )
RETURN
    INTERSECT ( D1Items; D2Items )

Regards,

Jimmy Tao

Murat62 · ‎09-05-2018

Hi Jimmy,

First thanks for your answer.

I tried your proposal. I am not getting the error message anymore but filter function with selectedvalue on date doesnot work properly.

In fact to trouble shoot i tried the following codes to create a table

D1 Items with Calculatetable = 
CALCULATETABLE(
        VALUES ( 'Fact Sales'[Customer_Id] );
        FILTER (
            'Fact Sales';
            'Fact Sales'[Date] = SELECTEDVALUE ( 'D1 date'[D1 Dates] )
        )
    )

D1 Items with Filter = FILTER('Fact Sales';
    'Fact Sales'[Date]=SELECTEDVALUE('D1 date'[D1 Dates]))

Then for both I am getting a blank table without any result.

It looks as selectedvalue is not matching with date from fact sales...

In any case again thanks for your support

Regards

v-yuta-msft · ‎09-16-2018

Hi Murat62,

You should use a var to save the result of calculate table, you can't directly create a calculatetable using selectedvalue because selectedvalue can only be used in a measure. In addtion, you said the measure above doesn't work properly, could you clarify more details about this?

Regards,

Jimmy Tao

Murat62 · ‎09-19-2018

Hi Jimmy,

Thanks again for your reply.

I did what you suggested without any success. I am getting blank tables

Please find formula used

D1 Items with Calculatetable = 
VAR D1 = SELECTEDVALUE('D1 date'[D1 Dates])
RETURN 
CALCULATETABLE(
        VALUES ( 'Fact Sales'[Customer_Id] );
        FILTER (
            'Fact Sales';
            'Fact Sales'[Date] = D1
        )
    )

It sounds no so easy to solve this issue...

PS: Not properly is meaning blank table without any rows

Thanks again for your support

Regards