The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello, I need to write a measure to do the following query.
SELECT count(*)
FROM table 1 a
WHERE a.code IN
(select b.code from table 2 b where b.id = selectedvalue(table3[id])
Appreciate any help/suggestions, Thank you!
Solved! Go to Solution.
Hi @Elichka
Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try this measure out
SelectedCount =
VAR SelectedID = SELECTEDVALUE(Table3[id])
VAR FilteredCodes =
CALCULATETABLE(
VALUES(Table2[code]),
Table2[id] = SelectedID
)
RETURN
CALCULATE(
COUNTROWS(Table1),
Table1[code] IN FilteredCodes
)
Thanks for a quick respond! When tried your solution it took loong time and got a message "Couldn't load the data for this visual". Table1 has more than 10 mln records and I have filters for visuals on the page. May be I need to add filters to the measure also? If you can add to your solution for table1 source_id = 1 and service_id = 2 ? Many thanks!
I tried to refactor the DAX
SelectedCount =
VAR SelectedID = SELECTEDVALUE(Table3[id])
RETURN
CALCULATE(
COUNTROWS(Table1),
FILTER(
Table1,
Table1[code] IN
CALCULATETABLE(
VALUES(Table2[code]),
Table2[id] = SelectedID
)
)
)
Thanks! I tried refactor measure and got nothing unfortunately...
Hi @Elichka
Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |