Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Hi,
I have this table where I need to count the Grand Total number of object by date:
Object Name | Object Type | Date |
abcvm1 | ahv | 3/26/2020 |
abcvm2 | vmw | 3/26/2020 |
bcdvm1 | ahv | 3/25/2020 |
bcdvm1 | ahv | 3/25/2020 |
bcdvm1 | ahv | 3/24/2020 |
bcdvm1 | vmw | 3/24/2020 |
cdwvm1 | ahv | 3/24/2020 |
An object should be counted only once per day if it's of the same object type. The answer should be:
Date | ahv | vmw | Total |
3/26/2020 | 1 | 1 | 2 |
3/25/2020 | 1 | 0 | 1 |
3/24/2020 | 2 | 1 | 3 |
Grand Total | 6 |
I am using a "Card" visualization to show the Grand Total as I filter the date/s. Please let me know the correct Measure that I can use for the "Card".
Thanks in advance!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi , @Rabur116
Try steps as below:
1.create a calculate table:
Table 2 = DISTINCT('Table'[Object Type])
2.create a mesure as below
value1 =
SUMX (
GROUPBY (
FILTER ( 'Table', 'Table'[Object Type] IN DISTINCT ( 'Table 2'[Object Type] ) ),
'Table'[Date],
'Table'[Object Type]
),
IF (
ISBLANK ( CALCULATE ( DISTINCTCOUNT ( 'Table'[Object Name] ) ) ),
0,
CALCULATE ( DISTINCTCOUNT ( 'Table'[Object Name] ) )
)
) + 0
It will show as below:
Here is a demo
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi , @Rabur116
Try steps as below:
1.create a calculate table:
Table 2 = DISTINCT('Table'[Object Type])
2.create a mesure as below
value1 =
SUMX (
GROUPBY (
FILTER ( 'Table', 'Table'[Object Type] IN DISTINCT ( 'Table 2'[Object Type] ) ),
'Table'[Date],
'Table'[Object Type]
),
IF (
ISBLANK ( CALCULATE ( DISTINCTCOUNT ( 'Table'[Object Name] ) ) ),
0,
CALCULATE ( DISTINCTCOUNT ( 'Table'[Object Name] ) )
)
) + 0
It will show as below:
Here is a demo
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, both the first person's solution and your solution work. Thanks a lot for the help!
You are welcome.
Hi,
You may download my PBI file from here.
Hope this helps.
Your solution works! Thanks a lot for the help.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
72 | |
67 | |
67 | |
42 | |
42 |
User | Count |
---|---|
46 | |
40 | |
28 | |
26 | |
25 |