Showing results for 
Search instead for 
Did you mean: 
DanBusIntel Member

Re: 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:


 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.

Stachu Super Contributor
Super Contributor

Re: Replicate this SQL in DAX

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 169 members 1,859 guests
Please welcome our newest community members: