Measure for select IN table where

[Elichka]

Hello, I have two issues with the report:

1. I have report with two pages, where selectedvalueonPage1 is drill trough to Page2, Page 1 and Page 2 have different filters.

And because of that selectedvalueonPage1 is using only for the measure to create count in #2 issue.

I can't get this value even it is displays on the page in the drill trough area.

2. I need to create measure for the following select statement

Select count(*) from table1
where  field1 in (select field1 from table2 where id= selectedvalueonPage1)

Any help / suggestions are greatly appreciate. Thanks!

[v-jialongy-msft]

Hi @Elichka 

For your first question, please check the following:
(1)Ensure that the drill-through filter on Page 2 is correctly set up to receive the value from Page 1.
(2)Verify that the field used for drill-through on Page 1 is also present and correctly configured on Page 2.

For your second question, here's a DAX formula that might help you achieve what you're looking for:

Measure = 
VAR SelectedID = SELECTEDVALUE(Table2[id])
RETURN
CALCULATE(
    COUNTROWS(Table1),
    FILTER(
        Table1,
        Table1[field1] IN CALCULATETABLE(VALUES(Table2[field1]), Table2[id] = SelectedID)
    )
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Elichka]

Hi Jayleny, Thank you so much for the quick respond!

The problem is that page1 is using different tables and filters to compare with page2.

That's why when the value comes from page1 to page2 using drill through i can see it in drill through area, but measure Selected = SELECTEDVALUE(table1[ID]) has nothing on page2. Any suggestions? Thanks!