Combine calculatetable with selectedvalue on date from slicers

[Murat62]

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]

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]

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]

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]

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

[Anonymous]

Dear Murat62,

I've the same issue. Have you solved yours in the meantime? If yes, I'd very appreciate if you shared the solution. Many thanks

[MarkSL]

Hi @Anonymous ,

I am having this exact same issue, did you find a solution??

Thanks!

Mark

[Anonymous]

Dear @MarkSL

I didn't use it with a calculatetable but another relatedtable.  Have a look at this subject https://community.powerbi.com/t5/Desktop/YTD-prior-with-filter-on-a-related-table/m-p/651403#M312404.

Probably it helps you. It filters the relatedtable by the date slicer.

Best regards