Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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,
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=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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |