Passing a table into a Filter using IN or some other method

[stufico]

Situation:

I have a fact table that has some unique rows of data that I need to isolate and integrate into some of my other equations.

The dataset is batch based and the batches contain multiple lines. Rows of data that I need to aggregate can only be identified by one of the lines of the batch, which is not one of the lines I need to aggregate.

My Solution:

I was able to solve the problem by creating a calculated dimension table like so:

Calculatetable(values(ftable[BatchID]), ftable[Issue] = "My problem", ftable[code] = "1")

I then created a calculated column using an if statement to match the transactions in the fact table to the dimension table and if they matched, then it was  "match" otherwise "no match"

If(ftable[Batch #] = Related(Dimension[Batch #]), "Match", "No Match")

I then added the sum of those with the match to my measure:

Total = Calculate([Total 1], Filter(ftable, fGL[Criteria1] = 1)) + Calculate([Total 2], Filter(ftable, ftable[match] = "Match"))

This seemed to work... but I really want to do this in less steps and maybe one measure?

I was thinking of :

Calculate([Total 1], Filter(ftable, fGL[Criteria1] = 1)) + Calculate([Total 2], Filter(ftable, ftable[Batch #]  IN {Calculatetable(values(ftable[BatchID]), ftable[Issue] = "My problem", ftable[code] = "1")})))

But that did not work

I also tried:

Calculate([Total 2], Filter(ftable, ftable[Batch #] IN Filter(values(ftable[BatchID]),ftable[Issue] = "My problem"&& ftable[code] = "1"))))

Does anyone have any different ideas?

[v-jiascu-msft]

Hi @stufico,

Could you mark the proper answer as a solution please?

Best Regards,

Dale

[GilbertQ]

Hi there

What you have done with the Calculated table as a dimension table is something that I would have done myself. I typically might have done that in the Power Query Editor in order to ensure that it comes into the model as a table.

Other than that I think it is the best solution, in that you model your data as required with facts and dimension tables. Which results in your final measure being fairly simple. Which means that the performance of the measure in your model will be quick.