topic Re: Distinctcount based on values in another column of same table – DAX for measure? in DAX Commands and Tips

Distinctcount based on values in another column of same table – DAX for measure?

Meraki_jeet — Fri, 02 Jun 2023 10:17:51 GMT

Hello Experts,

I am new to Power BI. Could you please help me with below? So below is my simplified table

resource_name | scan_date | Scan_time | server selected |
ABS 06-06-2023 1:00:00 AM SQL
BCS 06-06-2023 1:00:00 AM SQL
ADB 08-06-2023 8:00:00 AM Oracle

So here i need to find out the concurrency issue. As above you can see for resource_name "ABS" & "BCS" the scan_date, scan_time and server_selected are same. So in this scenario i have to highlight this and expecting a output like below either as measure or calculated column.

output required:
Scan_date | Concurrent issue |
06-06-2023 2

So I have to take count of distinct resource_name which have all same details

Thanks in advance!!

Re: Distinctcount based on values in another column of same table – DAX for measure?

some_bih — Sun, 04 Jun 2023 20:46:23 GMT

Hi Meraki_jeet your measure for distinctcount should be something like: DISTINCTCOUNT(Table[ColumnName]), so as I understand from your example it should be DISTINCTCOUNT(YourTableName[resource_name]), as resource_name is only column with 2 distinct value. i hope this help.

Re: Distinctcount based on values in another column of same table – DAX for measure?

Meraki_jeet — Mon, 05 Jun 2023 08:39:50 GMT

Hi some_bih , Thanks alot! I was able to get the above requirement with below formula,

Calculate(Distinctcount(TableName[resource_name]), ALLEXPECT(TableName, TableName[scan_date], TableName[Scan_time], TableName[server selected]))