Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Danielle_1
Frequent Visitor

Matching a result in a third column based on the selections in two other columns.

Hi,

I'm trying to create a measure where I need to generate a yes or no message to display in a card, based on the selection in 2 columns/slicers, and whether the results in the third column/rows (CSC) match each other or not (based on these 2 seperate selections)

 

The first column (Postcode Sector) has duplicate values and the second column (Branch Number) has many blanks. 

 

Danielle_1_0-1694448184054.png

 

I'm new to Power BI, so any help is appreciated.

Thanks in advance. 

 

 

1 ACCEPTED SOLUTION

@Danielle_1 

This won't work. As I mentioned earlier, you need to have each slicer from a separate disconnected table. You need to create two tables (SlicerTable1 and SlicerTable2). Both of them would be 

ALLNOBLANKROW ( 'Table'[Postcode Selector] )

then your measure would be

=
IF (
ISEMPTY (
EXCEPT (
CALCULATETABLE (
VALUES ( 'Table'[CSC] ),
TREATAS (
VALUES ( SlicerTable1[Postcode Selector] ),
'Table'[Postcode Selector]
)
),
CALCULATETABLE (
VALUES ( 'Table'[CSC] ),
TREATAS (
VALUES ( SlicerTable2[Postcode Selector] ),
'Table'[Postcode Selector]
)
)
)
),
"Yes",
"No"
)

View solution in original post

8 REPLIES 8
Danielle_1
Frequent Visitor

Hi @tamerj1 

 

Thanks for the response, unfortunately that didn't work. I added the measure to a card, selected CF83 3 for the postcode sector in the first slicer, and then selected branch 2810 in the second slicer (based on the data in the snapshot above), and the result was "Yes" when it should have been No, as "Cardiff" and "Bolton" don't match.

@Danielle_1 

Apparently I misunderstood the requirement. However, I'm now confused about the setup of your report visuals and slices and how did you setup the interactions between the visuals. 
you my however try

IF (

MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Postcode Selector] = SELECTEDVALUE ( 'Table'[Postcode Selector] ) ), 'Table'[CSC] )

= MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Branch Number] = SELECTEDVALUE ( 'Table'[Branch Number] ) ), 'Table'[CSC] ),

"Yes",

"No"

)

 

otherwise you might require to to have both slicer column each in a separate disconnected table. 

Hi @tamerj1 , I have my visuals set up as below. I have disabled the interaction between these 2 slicers so a branch number, and postcode can be selected individually. I placed 2 tables below to display the supporting CSC based on the selected branch/postal sector. I have tried your measure, and it didn't work. Appreciate the response. 

 

Danielle_1_1-1694534295340.png

 

 

@Danielle_1 

This won't work. As I mentioned earlier, you need to have each slicer from a separate disconnected table. You need to create two tables (SlicerTable1 and SlicerTable2). Both of them would be 

ALLNOBLANKROW ( 'Table'[Postcode Selector] )

then your measure would be

=
IF (
ISEMPTY (
EXCEPT (
CALCULATETABLE (
VALUES ( 'Table'[CSC] ),
TREATAS (
VALUES ( SlicerTable1[Postcode Selector] ),
'Table'[Postcode Selector]
)
),
CALCULATETABLE (
VALUES ( 'Table'[CSC] ),
TREATAS (
VALUES ( SlicerTable2[Postcode Selector] ),
'Table'[Postcode Selector]
)
)
)
),
"Yes",
"No"
)

Another question please, how do I display blank or 0 in the card if nothing is selected in the filters? Thanks

@Danielle_1 

=
IF (
NOT ISFILTERED ( SlicerTable1[Postcode Selector] )
&& NOT ISFILTERED ( SlicerTable2[Postcode Selector] ),
"",
IF (
ISEMPTY (
EXCEPT (
CALCULATETABLE (
VALUES ( 'Table'[CSC] ),
TREATAS (
VALUES ( SlicerTable1[Postcode Selector] ),
'Table'[Postcode Selector]
)
),
CALCULATETABLE (
VALUES ( 'Table'[CSC] ),
TREATAS (
VALUES ( SlicerTable2[Postcode Selector] ),
'Table'[Postcode Selector]
)
)
)
),
"Yes",
"No"
)
)

Hi @tamerj1  extremely grateful for your help, this has worked. Thanks so much. 

tamerj1
Super User
Super User

Hi @Danielle_1 

please try

IF (

DISTINCTCOUNT ( 'Table'[CSC] ) = 1,

"Yes",

"No"

)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors