Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
juliamacg_
Frequent Visitor

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

Hello, friends,

 

juliamacg__0-1667822377058.png

 

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!

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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

yingyinr_2-1667892001139.png

 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yiruan-msft
Community Support
Community Support

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

yingyinr_2-1667892001139.png

 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

||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!!

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

amitchandak
Super User
Super User

@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)

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors