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 3 years, 11 months ago by pietlinden.
• This reply was modified 3 years, 11 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 3 years, 11 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 3 years, 11 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 3 years, 11 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 3 years, 11 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...

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.

What do you mean "second temp table"? You can define multiple CTEs inside your expression, and then just use them both/all in the final one.

WITH cte1 (col1, col2, col3)

AS (SELECT...),

cte2(colA,...

COALESCE(column,0) ?