Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
53 |