Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
Try like
Sumx(summarize(Table[Date],table,[ Object Type]"_dist",distinct(Table[Object Name])),[_dist])
Hi,
You may download my PBI file from here.
Hope this helps.
Your solution works! Thanks a lot for the help.
Check out the November 2023 Power BI update to learn about new features.