Forum Discussion
Replicate this SQL in DAX
I've implemented what you've provided in your most recent post. I can see what you are doing, and if I am correct in that, I had to tweak the:
VALUES ( refCLERICAL_TOTAL_MARK[Id] )
to reference 'refCLERICAL_TOTAL_MARK[script_validation_id].
The problem is, when I use the measure but bring in other fields/columns from the tables in the data model it makes it so the IDs 11 and 12 are shown in the visual. This is the reason I was heading down the route of a calculated table. I wanted to restrict the rows by those that only appear in the SCRIPT_VALIDATION, CORRECTIONS, and CLERICAL_TOTAL_MARK tables. Put these restricted rows in a table and then the user can swap fields/columns from the calcated table.
Right now, I can't see the wood for the trees.
I'm digesting your queries to understand different techniques. Although it's not doing as I wanted, I'll be suprised if what I want isn't acheivable but I don't know how to acheive it at the moment, I will give you a thumbs up (kudos) because you have been extremely helpful. Thanks.
hmm, I see few ways of approaching this:
1) include in every measure you show:
INTERSECT (
ALL ( SCRIPT_VALIDATION[id] ),
VALUES ( refCLERICAL_TOTAL_MARK[script_validation_id] )
)which is annoying
2) removing the troublesome ids in PowerQuery - but most likely it's not an option? do you need them in other views/visuals?
3) adding the piece of code I posted earlier to yor calculated table