Forum Discussion

djskro's avatar
djskro
Frequent Visitor
1 year ago

Erroneous Results from COUNTROWS() in Context-transition

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:

 

 

2 Replies

    • djskro's avatar
      djskro
      Frequent Visitor

      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.