Help with GROUP BY and measures (translate from SQL code)

[Anonymous]

Hello guys, 

I need to do something like this SQL code inside POWER BI:
Basically I'm converting "facturacion" to negative when certain conditions are met.

SELECT 
	anunciante, 
	SUM(
		CASE 
			WHEN ano_cambio ='2019'
			THEN 
				CASE
					WHEN agencia_original = 'Agencia1' 
					THEN facturacion*-1 
					ELSE facturacion
				END
			ELSE
				CASE
					WHEN (agencia_original = 'Agencia1' AND agencia_modificada = '') OR agencia_modificada = 'Agencia1'
					THEN facturacion
					ELSE facturacion*-1 
				END
		END
	) AS FACT	
FROM test.test_agencias_centrales 
WHERE ((agencia_original='Agencia1' AND agencia_modificada!='') OR agencia_modificada='Agencia1') AND EXTRACT(YEAR FROM fecha)=2019 AND EXTRACT(day FROM fecha)<=5
GROUP BY anunciante

Take into account that 'Agencia1' comes from a filter (dim_agencia[Agencia] in POWER BI), same for the 'fecha' which means date in spanish (calendario[date]).

The SQL code isn't complex at all, but I gues I'm missing a lot of DAX learning here...

I'm struggling a lot with the CASE part and the grouping part since I read that GROUPBY can't go inside a measure, I'm not sure if I'm wrong. 

Inside the pbix there are two measures which produce good results for anunciante, but totals doesn't work. These measures are just an example of how I'm trying to do it, but I'm sure I'm far from doing it right and I want to do it right not with FIRSTNONBLANK() because there can be more values.

Here is a pbix with sample data:
https://drive.google.com/open?id=1vMDX6EA0oW95a33bMbAEqunBcylXDyBp

Here are some results expected, the above SQL works and gave me those results too:

I don't need you to solve the whole problem but if you can give me a hint I would appreciate it. I've been trying to do this with SUMMARIZE, GROUPBY, ETC... but didn't manage to make it work...

Apart from that, if you downloaded you can see there is Agencia (group), that means when comparing inside the measure it souldn't be "=", it should be "IN" and then list the results selected in Agencia filter, but I can work this out if I can manage to understand the other things. Just saying in case you wonder why there is this filter inside the pbix sample.

If I'm missing something or need anything else just ask.

Thank you so much!!

[v-eachen-msft]

Hi @Anonymous ,

I used ISINSCOPE() function based on your orginal measures. You could refer to the following DAX:

Measure =
VAR a =
    SUMX ( ALLSELECTED ( 'Table'[ANUNCIANTE] ), [Fact. Acumulado] )
RETURN
    IF ( ISINSCOPE ( 'Table'[ANUNCIANTE] ), [Fact. Acumulado], a )

Measure 2 =
VAR a =
    SUMX ( ALLSELECTED ( 'Table'[ANUNCIANTE] ), [Fact. año completo] )
RETURN
    IF ( ISINSCOPE ( 'Table'[ANUNCIANTE] ), [Fact. año completo], a )

Here is the results.

[mwegener]

Hi @Anonymous ,

check this out.

Fact. Acumulado =
TOTALYTD (
    SUMX (
        FILTER (
            'Table',
            (
                'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] )
                    && 'Table'[AGENCIA_MODIFICADA] <> ""
            )
                || 'Table'[AGENCIA_MODIFICADA] = SELECTEDVALUE ( Dim_agencia[Agencia] )
        ),
        IF (
            'Table'[AÑO_CAMBIO] = "2019",
            IF (
                'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] ),
                'Table'[FACTURACION] * -1,
                'Table'[FACTURACION]
            ),
            IF (
                (
                    'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] )
                        && 'Table'[AGENCIA_MODIFICADA] = ""
                )
                    || 'Table'[AGENCIA_MODIFICADA] = SELECTEDVALUE ( Dim_agencia[Agencia] ),
                'Table'[FACTURACION],
                'Table'[FACTURACION] * -1
            )
        )
    ),
    DATEADD ( 'Calendario'[Date], -1, YEAR )
)

Fact. año completo =
CALCULATE (
    SUMX (
        FILTER (
            'Table',
            (
                'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] )
                    && 'Table'[AGENCIA_MODIFICADA] <> ""
            )
                || 'Table'[AGENCIA_MODIFICADA] = SELECTEDVALUE ( Dim_agencia[Agencia] )
        ),
        IF (
            'Table'[AÑO_CAMBIO] = "2019",
            IF (
                'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] ),
                'Table'[FACTURACION] * -1,
                'Table'[FACTURACION]
            ),
            IF (
                (
                    'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] )
                        && 'Table'[AGENCIA_MODIFICADA] = ""
                )
                    || 'Table'[AGENCIA_MODIFICADA] = SELECTEDVALUE ( Dim_agencia[Agencia] ),
                'Table'[FACTURACION],
                'Table'[FACTURACION] * -1
            )
        )
    ),
    FILTER ( ALL ( Calendario ), Calendario[Año] = MAX ( Calendario[Año] ) - 1 )
)