Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Murat62
Regular Visitor

Combine calculatetable with selectedvalue on date from slicers

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

 

7 REPLIES 7
v-yuta-msft
Community Support
Community Support

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

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 

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

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
Not applicable

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

Hi @Anonymous ,

 

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

 

Thanks!

 

Mark

Anonymous
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.