TREATAS vs INTERSECT

[Anonymous]

Hello,

Below, is the DAX syntax I came up with to get a listing of all the SAP hours by the people who

1) speaks Polish (PL-PL)

2) spent more time on FQA than LQA projects

Table = 
VAR A = CALCULATETABLE(SAP, Employees[Main LQA Lang]="PL-PL")
VAR B = ADDCOLUMNS(
    SUMMARIZE(A, SAP[Resource Name]),
    "FQA", CALCULATE(SUM(SAP[H]),SAP[Project Type]="FQA", Cal[Year]=2019),
    "LQA", CALCULATE(SUM(SAP[H]),SAP[Project Type]="LQA", Cal[Year]=2019)
)
VAR C = FILTER(B,[LQA]>[FQA])
VAR D = SELECTCOLUMNS(C,"Resource Name",[Resource Name])
VAR E = CALCULATETABLE(SAP,INTERSECT(VALUES(SAP[Resource Name]),D))

RETURN
E

This seems to give me what I need. However, I read somewhere that TREATAS is better/faster than INTERSECT.

If i replace the VAR E line by:

VAR E = CALCULATETABLE(SAP,TREATAS(VALUES(SAP[Resource Name]),D))

I get this error message "Function TREATAS expects a fully qualified column reference as argument number 2"

If I change D by D[Resource Name] it doesn't work either as D is a virtual table from the previous VAR line.

Is there a way to change my syntax so I can use TREATAS?

Thanks!

[Anonymous]

By the way... It's very bad you are hard-coding values like 2019 (and maybe even "PL-PL") into your measures. This leads to rigid code that will be hard to maintain over time.

I can also see that project types are not stored in a dimension but in the fact table. Another bad practice that might bite you when you least expect it...

Another bad practice is naming your variables with meaningless identifiers.

Fact tables should only ever store keys to dimensions and measures of the process you're modeling. Dimensions should store all the attributes that you want to slice the measures by. This leads to a clean design, fast DAX and no hidden problems that will rear their ugly head when you have a "dirty" model and - what's worse - when you least expect it. Please do yourself a favour and get rid of all the bad practices you are following right now. You'll thank me later 🙂

Best
Darek

[Anonymous]

Thanks @Anonymous

This was a first draft 🙂 I reworked on it since then and decided to go with a measure instead instead of a table. Let me know what you think of this one (yes, I still have to not hardcoding the year, but for PL-PL, this is a special request from my cust as he wants something special for that language)

H2 =

VAR CurYear = 2019

RETURN

CALCULATE (

            SUM(SAP[H]),

            UNION (

                FILTER (

                    Employees,

                    Employees[Main LQA Lang] = "PL-PL"

                    && CALCULATE (SUM ( SAP[H] ), ALL ( Cal ), Cal[Year] = CurYear, ALL('Accounts List Light'), 'Project List'[Project Type] = "LQA")

                        > CALCULATE (SUM ( SAP[H] ), ALL ( Cal ), Cal[Year] = CurYear, ALL('Accounts List Light'), 'Project List'[Project Type] = "FQA")

                ),

                FILTER (

                    Employees,

                    Employees[Main LQA Lang] <> "PL-PL"

                        && NOT ISBLANK ( Employees[Main LQA Lang] )

                        && Employees[Main LQA Lang]<>""

                )

            ),

            'Project List'[Project Type] = "FQA"

        )

[Anonymous]

// please find a good name for this,
// like [Total H] or similar...
[Main Measure] = SUM ( SAP[H] ) 

// Please note that CALCULATETABLE is most of the time
// more efficient than FILTER. Also, it's a bad idea
// to filter by a whole table when filtering on a field
// is enough.

H2 =
// The year should be coming from a slicer
// or a filter to make this calculation
// flexible.
VAR __currentYear = 2019
var __empSet1 =
    FILTER (
    	CALCULATETABLE(
        	VALUES( Employees[EmployeeID] ),
        	KEEPFILTERS( Employees[Main LQA Lang] = "PL-PL" )
        ),
        
        CALCULATE(
                // It would be best to turn the two CALCULATE'S into
                // measures on their own, say, [Total H LQA] and
                // [Total H FQA] and use them here instead of
                // the expressions. Consider using KEEPFILTERS for the
                // conditions under the two CALCULATE's. But what
                // you want to do depends on what you want to achieve...
	        CALCULATE(
	            [Main Measure],
	            'Project List'[Project Type] = "LQA"
	        )
	        >
	        CALCULATE(
	            [Main Measure],
	            'Project List'[Project Type] = "FQA"
	        ),
	        
                Cal[Year] = __currentYear,
                ALL ( 'Accounts List Light' ),
                ALL ( Cal )
	    )
    )
var __empSet2 =
	CALCULATETABLE(
        VALUES( Employees[EmployeeId] ),
        KEEPFILTERS(
            true()
            && Employees[Main LQA Lang] <> "PL-PL" )
            // please note that BLANK() = ""
            && Employees[Main LQA Lang] <> ""
       	)
    )
var __empUnion =
    UNION (
	__set1,
	__set2
    )	
RETURN
    CALCULATE (
        [Main Measure],   
	__empUnion,		
        'Project List'[Project Type] = "FQA",
        ALL( Employees )
    )

Please, as usual TEST this measure extensively. You might need to adjust some bits and pieces like, say, KEEPFILTERS.

Best

D.

[Anonymous]

Hi @Anonymous 

The customer added another complexity layer to his request: as they may select more than a month in the month slicer, the expected filtering should change per the comments in the DAX syntax below.

This is what I came up with and it seems to do the work.

Besides replacing FILTER by CALCULATETABLE per your suggestion and filter on one field instead of the whole dimension (and also, work on the CurYear), is there a way to simplify this syntax. I couldn't find a way but to add two calculates. 

Also, I never understood KEEPFILTERS, I read about it but I don't get it. Could you explain?

Thanks for the help!

[Total H] := VAR CurYear = 2019

VAR LastMonthOfSelectedPeriod = CALCULATE(MAX(Cal[YearMonth]),ALLSELECTED(Cal))

RETURN

//if this is not last month of selected period then pick PL-PL: [LQA]>[FQA], Others: Everybody

CALCULATE(SUM(SAP[H]),

    'Project List'[Project Type] = "FQA",

    FILTER(Cal, Cal[YearMonth]<>LastMonthOfSelectedPeriod),

    UNION(

        FILTER(Employees,

            Employees[Main LQA Lang] = "PL-PL"

            && CALCULATE (SUM ( SAP[H] ), ALL ( Cal ), Cal[Year] = CurYear, ALL('Accounts List Light'), 'Project List'[Project Type] = "LQA")

                > CALCULATE (SUM ( SAP[H] ), ALL ( Cal ), Cal[Year] = CurYear, ALL('Accounts List Light'), 'Project List'[Project Type] = "FQA")

        ),

        FILTER(Employees,

            Employees[Main LQA Lang] <> "PL-PL"

                && NOT ISBLANK ( Employees[Main LQA Lang] )

                && Employees[Main LQA Lang]<>""

        )

    )

)

+

//if this is the last month of selected period then pick everybody with a Main LQA Lang

CALCULATE(SUM(SAP[H]),

    'Project List'[Project Type] = "FQA",

    FILTER(Cal, Cal[YearMonth]=LastMonthOfSelectedPeriod),

    FILTER(Employees,

        NOT ISBLANK ( Employees[Main LQA Lang] )

        && Employees[Main LQA Lang]<>""

    )

)

[Anonymous]

TREATAS tells you exactly what the problem is. You cannot use a table stored in VAR in the second argument. There does not exist a physical table D with a column name of [Resource Name]. You can use TREATAS only on real tables.

Best
Darek