cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 Blog -Week is not so Weak Proud to be a Super User! Connect on 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 Blog -Week is not so Weak Proud to be a Super User! Connect on 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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors