Forum Discussion

_Junayetrahman's avatar
_Junayetrahman
Frequent Visitor
2 years ago
Solved

Count based on condition

Hi all, I need solution for this condition attached in screenshot.

Theere is multiple same BD CODE when filtering V P.doc also has only one BD CODE. I need to take the distinct count of BD CODE and then I need to substract the distict count with the count that has only single row. Please refrer screenshot for more clarification.

Please help. Thanks.

Need Help
Show and Tell
Tips and Tricks
  • TomMartens's avatar
    TomMartens
    2 years ago

    Hey _Junayetrahman ,

     

    consider creating a pbix file that contains sample data, but still reflect your data model: tables, relationships, calculated columns and measures. Upload the pbix to onedrive, google drive, or dropbox and share the link. If you are using a spreadsheet to create the sample data instead of the manual input method share the spreadsheet as well.

     

    Do not forget to describe the expected result based on the sample data you provide. Also add data visualizations to a report page and describe how you expect the result is "mapped" to the visual(s).

     

    Regards,

    Tom

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi _Junayetrahman ,

    You can create a measure as below to get it, please find the details in the attachment.

    Count of repeated DB Code = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[BD CODE],
        "@count",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[V Code] ),
                FILTER ( 'Table', 'Table'[BD CODE] = EARLIER ( 'Table'[BD CODE] ) )
            )
    )
VAR _count =
    SUMX ( _tab, [@count] )
RETURN
    COUNTROWS ( FILTER ( _tab, [@count] > 1 ) )

    Best Regards

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    Hey _Junayetrahman ,

     

    consider creating a pbix file that contains sample data, but still reflect your data model: tables, relationships, calculated columns and measures. Upload the pbix to onedrive, google drive, or dropbox and share the link. If you are using a spreadsheet to create the sample data instead of the manual input method share the spreadsheet as well.

     

    Do not forget to describe the expected result based on the sample data you provide. Also add data visualizations to a report page and describe how you expect the result is "mapped" to the visual(s).

     

    Regards,

    Tom

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi _Junayetrahman ,

    You can create a measure as below to get it, please find the details in the attachment.

    Count of repeated DB Code = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[BD CODE],
        "@count",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[V Code] ),
                FILTER ( 'Table', 'Table'[BD CODE] = EARLIER ( 'Table'[BD CODE] ) )
            )
    )
VAR _count =
    SUMX ( _tab, [@count] )
RETURN
    COUNTROWS ( FILTER ( _tab, [@count] > 1 ) )

    Best Regards