Forum Discussion
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 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
Please find the solution at
https://www.dropbox.com/s/w163vb8y4rjc4ds/overallflag.pbix?dl=0Appreciate 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 LinkedinHi 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,
3 Replies
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,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.
Please find the solution at
https://www.dropbox.com/s/w163vb8y4rjc4ds/overallflag.pbix?dl=0Appreciate 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
