You would have to use two separate queries to accomplish it if you replace the LEFT JOIN.

UPDATE r
SET r.HasChemical=CASE WHEN cm.partid IS NOT NULL THEN 'HasChemical' ELSE...

• This reply was modified 4 years, 4 months ago by pietlinden.

You would have to filter those out in the WHERE clause in the inner query.

SELECT FIRSTVALUE(…)

FROM (Select…WHERE elementID !=‘C’)

Disassemble the CTE into single queries and run them one at a time?

insert the intermediate results into a temporary table once you fix them and then query tempdb?

I would just create the proper tables (not munging all the data into a single table) and then put in fake data to test it. So maybe 3-4 records in...

I have a DB full of RACES and RUNNERS, COURSES, STATS etc etc. One horse could appear in the table dozens of times

I think you may be missing a table....

That's the weird thing about DAX / PowerBI... the reports are fully interactive. You can get data from a stored procedure or view that has no aggregates at all, and...

• This reply was modified 4 years, 4 months ago by pietlinden.
• This reply was modified 4 years, 4 months ago by pietlinden.

Except I'm not talking about normal SQL Server/T-SQL, except to get data out of SQL Server and into the data model. I'm talking about DAX. =)

Here are the measures for...

• This reply was modified 4 years, 4 months ago by pietlinden.

I was pointing that out to the OP... Maybe read a little of The Data Warehouse Toolkit to learn how to build star schemas might be in order. I didn't...

I think writing summary queries in T-SQL is the wrong way to go if you're consuming the data in PowerBI. It's fine to create views using T-SQL to populate your...

This is super handy!

I should use this to create a super simple PowerBI file and write all the measures there, so you can see how it works. The reason I...

• This reply was modified 4 years, 4 months ago by pietlinden.

You're welcome.

I was asking because if you're doing this in PowerBI, and using DAX, then you should use a star schema instead of an OLTP one. The easiest way of...

• This reply was modified 4 years, 4 months ago by pietlinden.

DATE2 >= '01-01-2021' AND DATE2 <'01-01-2022' ORDER BY DATE2 DESC;

Please read: Forum Etiquette: How to post data/code on a forum to get the best help

You can script table creation and inserts and copy them to the clipboard and...

I was asking because you can do things in DAX that are crazy hard in SQL. Like removing filters by using the ALL() function.

If you have to learn DAX, Rob...

• This reply was modified 4 years, 4 months ago by pietlinden.

I am inserting SQL code into a data visualization tool

Which one? If you're using Excel or PowerBI, you can very likely use DAX for this. (Except DAX is not like...