Count re-occurrences of a value related to 2 other columns

[juliamacg_]

Hello, friends,

I'm looking for a way to count the re-occurrences of a value in column STEP which share the same value in column CODE and whose value in NAME is not blank. 

In the case of the Excel table above, the DAX formula I'd like to have would return the value 2, because:

• CODE "001" has no re-occurrences in STEP except for the NAME field in blank, which should not count;
• CODE "002" has one re-occurrence for STEP "3" with "JOHN" and "JULIA" in the NAME column; there's another reoccurrence for STEP "2" but with a NAME field in blank, which should not count;
• and CODE "003" has one re-occurrence for STEP "3" with again "JOHN" and "JULIA" in the NAME fields; there's another reoccurrence for STEP "2" but with a NAME field in blank, which should not count.

Sorry for the messy idea but I hope you can help! Thanks in advance!

[v-yiruan-msft · Accepted solution]

Hi @Manal_Edd ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create two measures as below

Measure = 
VAR _selcode =
    SELECTEDVALUE ( 'Table'[CODE] )
VAR _selstep =
    SELECTEDVALUE ( 'Table'[STEP] )
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[NAME] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[CODE] = _selcode
                && 'Table'[STEP] = _selstep
                && TRIM ( 'Table'[NAME] ) <> ""
        )
    )
RETURN
    IF ( _count > 1, 1, 0 )

Re-occurrences = SUMX ( GROUPBY ( 'Table', 'Table'[CODE], 'Table'[STEP] ), [Measure] )

2. Create a card visual

If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Meraki_jeet]

||Hi @v-yiruan-msft, I am new to Power BI and I have similar kind of problem. 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 in table, 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!!

[juliamacg_]

I think that did the work! Thank you so much!

[amitchandak]

@juliamacg_ , Try a new column like

New column =

var _cnt = countx(filter(Table, [Code] = earlier([Code]) && [Step] = earlier([Step]) && not(isblank([Name])) ), [Name])

return

if(_cnt >1, 1, 0)

[juliamacg_]

Hello @amitchandak 

Thank you very much for your reply! However I didn't quite understand what the column does. Could you help me use it? Thank you!