Search code examples
powerbidaxetlmeasure

a measures for comparing two values and counting the result in Power BI


How can I write a measure to count the number of userID for which sum(x1) is equal to count(order_id), in Power BI?

For example, my data table is:

userID x1 order_id
141 1 719
172 0 616
172 0 189
172 0 2211
172 0 317
1103 1 98
1103 1 213
1103 1 15
2524 0 4902
2524 1 3620

and I use table visual of power bi for this, to explain my mean:

userID sum(x1) count(order_id)
141 1 1
172 0 4
1103 3 3
2524 1 2

Note that the userID column is one of the columns in my data table, and calculating sum(x1) and count(order_id) in this sample is by Power BI default features.

The result for this sample should be 2. I need a measure that returns 2.


Solution

  • Measure1 = 
    VAR _base1 =
        SUMMARIZE ( 'Table 1', 'Table 1'[userID] )
    VAR _base2 =
        ALLEXCEPT ( 'Table 1', 'Table 1'[userID] )
    VAR _ct =
        ADDCOLUMNS ( _base1, "X", CALCULATE ( COUNT ( 'Table 1'[order_id] ), _base2 ) )
    VAR _sum =
        ADDCOLUMNS ( _base1, "X", CALCULATE ( SUM ( 'Table 1'[x1] ), _base2 ) )
    VAR _nt =
        NATURALINNERJOIN ( _sum, _ct )
    RETURN
        COUNTROWS ( _nt )
    

    Solution

    or

    Measure4 =
    VAR _1 =
        COUNTX (
            VALUES ( 'Table 1'[userID] ),
            VAR _base =
                ALLEXCEPT ( 'Table 1', 'Table 1'[userID] )
            VAR _1 =
                CALCULATE ( SUM ( 'Table 1'[x1] ), _base )
            VAR _2 =
                CALCULATE ( COUNTROWS ( 'Table 1' ), _base )
            VAR _3 =
                IF ( _1 = _2, 1 )
            RETURN
                _3
        )
    RETURN
        _1
    

    Solution2