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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |