Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have this sample table:
I want to calculate the count of users, which have two different stores . I use direct query, so i can not use summerize or groub by.
I've tried this code but it needs a shot, which i can not find.
Count of users with two stores = CALCULATE(DISTINCTCOUNT('table'[UserId]);FILTER(ALLSELECTED(table'[UserId]);DISTINCTCOUNT('table'[Store])=2))
Could someone help me please 🙂
Taher
Solved! Go to Solution.
Try this MEASURE
Measure = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[UserID], "Count", DISTINCTCOUNT ( 'Table'[StoreID] ) ), [Count] = 2 ) )
Sorry I didn't read you can't use summarize
Try this
Measure 2 = COUNTX ( FILTER ( ALL ( 'Table'[UserID] ), CALCULATE ( DISTINCTCOUNT ( 'Table'[StoreID] ) ) = 2 ), CALCULATE ( DISTINCTCOUNT ( 'Table'[StoreID] ) ) )
Try this MEASURE
Measure = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[UserID], "Count", DISTINCTCOUNT ( 'Table'[StoreID] ) ), [Count] = 2 ) )
Hi @Zubair_Muhammad,
thank u very much. You have really saved me with this measure 🙂
The measure with SUMMERIZE WORKED WELL ( I DONT KNOW HOW WITH DIRECT QUERY !!).
I'll try the second measure as well.
I'm very gratefull, thnx a lot 🙂
Taher
Sorry I didn't read you can't use summarize
Try this
Measure 2 = COUNTX ( FILTER ( ALL ( 'Table'[UserID] ), CALCULATE ( DISTINCTCOUNT ( 'Table'[StoreID] ) ) = 2 ), CALCULATE ( DISTINCTCOUNT ( 'Table'[StoreID] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |