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.
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 )
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