The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024. 2-for-1 sale on June 20 only!
Register NowGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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] ) ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
92 | |
90 | |
72 | |
69 |
User | Count |
---|---|
232 | |
128 | |
117 | |
82 | |
82 |