cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ssharm43
Frequent Visitor

calculated column distinct count with condition

Hello everyone,

If have to create a filter based on Flag "Yes" or "No", so that can happen only from calculated column.

below is the scenario :

 

I have table like this :

Customer IDCase NameFlag
1ayes
1bno
1cno
2ayes
2byes
2cno
3ano
3bno
3cno
4ano
4bno
4bno

 

I want filter on Flag for "Yes" or "No"

 

if Flag is "Yes" then output should be like:

 

customer IdCase NameFlag
1aYes
2ayes

 

and if Flag is "No" then output should be like:

 

expected output  
 customer IdCase NameFlag
 3ano
 3bno
 3cno
 4ano
 4bno
 4bno

 

i.e. If Flag is No for all the cases Id should the record should not be shown else it should be shown.

 

if anyone come with solution please help me out.

 

Thank you

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: calculated column distinct count with condition

Please find the solution at

https://www.dropbox.com/s/w163vb8y4rjc4ds/overallflag.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

Community Support
Community Support

Re: calculated column distinct count with condition

Hi @ssharm43 ,

 

We can try to use the following measure in visual filter to meet your requirement:

 

Visual Control = 
IF (
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Flag] ), ALLSELECTED ( 'Table' ) )
        <> CALCULATE ( DISTINCTCOUNT ( 'Table'[Flag] ), ALL ( 'Table' ) ),
    IF (
        SELECTEDVALUE ( 'Table'[Flag] ) = "no"
            && CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    CALCULATETABLE(ALLSELECTED('Table'),ALL('Table'[Flag])),
                    'Table'[Customer ID] IN FILTERS ( 'Table'[Customer ID] )
                        && 'Table'[Flag] = "yes"
                )
            ) = 0,
        1,
        IF (
            SELECTEDVALUE ( 'Table'[Flag] ) = "yes"
                && CALCULATE (
                    MIN ( 'Table'[Case Name] ),
                    FILTER (
                        ALLSELECTED('Table'),
                        'Table'[Customer ID] IN FILTERS ( 'Table'[Customer ID] )
                            && 'Table'[Flag] = "yes"
                    )
                )
                    in FILTERS ( 'Table'[Case Name] ),
            1,
            -1
        )
    ),
    1
)

 

1.jpg2.jpg

 


Best regards,

 

Community Support Team _ Dong Li
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

3 REPLIES 3
Super User IV
Super User IV

Re: calculated column distinct count with condition

Please find the solution at

https://www.dropbox.com/s/w163vb8y4rjc4ds/overallflag.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

Community Support
Community Support

Re: calculated column distinct count with condition

Hi @ssharm43 ,

 

We can try to use the following measure in visual filter to meet your requirement:

 

Visual Control = 
IF (
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Flag] ), ALLSELECTED ( 'Table' ) )
        <> CALCULATE ( DISTINCTCOUNT ( 'Table'[Flag] ), ALL ( 'Table' ) ),
    IF (
        SELECTEDVALUE ( 'Table'[Flag] ) = "no"
            && CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    CALCULATETABLE(ALLSELECTED('Table'),ALL('Table'[Flag])),
                    'Table'[Customer ID] IN FILTERS ( 'Table'[Customer ID] )
                        && 'Table'[Flag] = "yes"
                )
            ) = 0,
        1,
        IF (
            SELECTEDVALUE ( 'Table'[Flag] ) = "yes"
                && CALCULATE (
                    MIN ( 'Table'[Case Name] ),
                    FILTER (
                        ALLSELECTED('Table'),
                        'Table'[Customer ID] IN FILTERS ( 'Table'[Customer ID] )
                            && 'Table'[Flag] = "yes"
                    )
                )
                    in FILTERS ( 'Table'[Case Name] ),
            1,
            -1
        )
    ),
    1
)

 

1.jpg2.jpg

 


Best regards,

 

Community Support Team _ Dong Li
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

ssharm43
Frequent Visitor

Re: calculated column distinct count with condition

Thank you all for your valuable time and comments on my requirment.

Both of the solution solved my issue.

 

Thank you again for such great community help.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors