Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
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
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
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