Erroneous Results from COUNTROWS() in Context-transition

[djskro]

Unless I'm missing something, I'm pretty sure this isn't supposed to happen:

EVALUATE
CALCULATETABLE (
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( Users[User ID] ),
            "Test Expression 1 - CORRECT",
                CALCULATE (
                    CONCATENATEX (
                        FILTER (
                            VALUES ( 'Privilege Conflicts'[Privilege 2 ID] ),
                            'Privilege Conflicts'[Privilege 2 ID]
                                IN VALUES ( Privileges[Privilege ID] )
                        ),
                        'Privilege Conflicts'[Privilege 2 ID],
                        " | "
                    )
                ),
            "Test Expression 2 - INCORRECT",
                CALCULATE (
                    COUNTROWS (
                        FILTER (
                            VALUES ( 'Privilege Conflicts'[Privilege 2 ID] ),
                            'Privilege Conflicts'[Privilege 2 ID]
                                IN VALUES ( Privileges[Privilege ID] )
                        )
                    )
                )
        ),
        CROSSFILTER ( 'Users and Privileges'[Privilege ID], Privileges[Privilege ID], BOTH )
    ),
    Users[User ID] IN { 102, 103 }
)

Results:

Notice how, for User ID = 102, the results in the test expressions are inconsistent, despite the fact that they are semantically basically the same.

For reference, note that the following produces expected results:

EVALUATE
CALCULATETABLE(
    CALCULATETABLE(
        {
            ( 
	            
	            -- Test Expression 1 - CORRECT
	            
	            CALCULATE(
	                CONCATENATEX(
	                    FILTER(
	                        VALUES( 'Privilege Conflicts'[Privilege 2 ID] ),
	                        'Privilege Conflicts'[Privilege 2 ID]
	                            IN VALUES( Privileges[Privilege ID] )
	                    ),
	                    'Privilege Conflicts'[Privilege 2 ID],
	                    " | "
	                ),
	                Users[User ID] = 102
	            ),
	            
	            -- Test Expression 2 - CORRECT (inconsistent with prior query)
	            
	            CALCULATE(
	                COUNTROWS(
	                    FILTER(
	                        VALUES( 'Privilege Conflicts'[Privilege 2 ID] ),
	                        'Privilege Conflicts'[Privilege 2 ID]
	                            IN VALUES( Privileges[Privilege ID] )
	                    )
	                ),
	                Users[User ID] = 102
	            ) 
            )
        },
        CROSSFILTER ( 'Users and Privileges'[Privilege ID], Privileges[Privilege ID], BOTH )
    ),
    Users[User ID] IN { 102, 103 }
)

Results:

---

It appears that something might not be functioning correctly between the context-transition and COUNTROWS(), but not fully sure.

@marcorusso @Jeffreywang 

---

Power BI version: 2.139.2054.0

Data model:

[Anonymous]

Hi, @djskro 

Can you provide example files and the output you expect? This will better help you solve the problem. Please remove any sensitive data in advance.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[djskro]

Thank you for your reply.

I believe that this problem is evident from the information I've provided. In my first DAX statement, semantically speaking, "Test Expression 1" should not differ from "Test Expression 2" for User ID 102.

Would you not agree that, semantically, the results I've shown are problematic? If so, it seems that the appropriate next step would not be dependent on my particular PBIX file.