Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
Say I am working on the datamodel found in this report :
https://tinyurl.com/2upv9t53
I want to create a similar table to this, where distinct count of customerIds are displayed for all rows (in this case it would be the number of customerIds in the category group, and ignore all filters on the customers table. So the expected outcome would be something like this:
I have tried the formula in the screenshot below, but it seems to give me a cartesian product of some sort.
Here is the Customer table showing its mapping with the customergroup table:
Solved! Go to Solution.
@bk-ikram Try the following. PBIX attached below signature.
Measure =
VAR __Group = MAX( 'Customer'[CategoryGroup] )
VAR __Sales = SUM( 'Sales'[Sales] )
VAR __CountCustomers =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER(
ALLSELECTED('Customer'),
[CategoryGroup] = __Group
),
"__Customer", [Customer Name]
)
)
)
VAR __Result = IF( __Sales = BLANK(), BLANK(), __CountCustomers )
RETURN
__Result
@bk-ikram Try the following. PBIX attached below signature.
Measure =
VAR __Group = MAX( 'Customer'[CategoryGroup] )
VAR __Sales = SUM( 'Sales'[Sales] )
VAR __CountCustomers =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER(
ALLSELECTED('Customer'),
[CategoryGroup] = __Group
),
"__Customer", [Customer Name]
)
)
)
VAR __Result = IF( __Sales = BLANK(), BLANK(), __CountCustomers )
RETURN
__Result
Thank you Greg, this solution seems to do the trick. I just thought that there could be a more "straight-forward " solution. I would appreciate it if you could briefly explain why the formula I tried would not work.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
29 | |
27 | |
24 | |
22 |