calculated column distinct count with condition

[ssharm43]

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 ID	Case Name	Flag
1	a	yes
1	b	no
1	c	no
2	a	yes
2	b	yes
2	c	no
3	a	no
3	b	no
3	c	no
4	a	no
4	b	no
4	b	no

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

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

customer Id	Case Name	Flag
1	a	Yes
2	a	yes

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

expected output
	customer Id	Case Name	Flag
	3	a	no
	3	b	no
	3	c	no
	4	a	no
	4	b	no
	4	b	no

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

[amitchandak · Accepted solution]

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

[v-lid-msft · Accepted solution]

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
)

Best regards,

[ssharm43]

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.

[amitchandak · Accepted solution]

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