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 ago by pietlinden.
• This reply was modified 4 years 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 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 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 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 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...

Since a MERGE is basically an INSERT/UPDATE, and you have 3 possible caes, you might want to just skip the MERGE and use separate INSERT/UPDATE statements.

IF (TestInvalid)

BEGIN

INSERT INTO FailTable()....

END

ELSE

IF EXISTS().... ...

If it's not working when it's crazy complex, why not simplify and built it a piece at a time?

The error message is pretty clear. Any time you create a totals/aggregate...

IF <test>

BEGIN

SELECT... FROM...WHERE... <params>

END

ELSE

SELECT...FROM WHERE <different params>

There's nothing stopping you from forking the two queries with an IF statement.