topic Re: Matching a result in a third column based on the selections in two other columns. in DAX Commands and Tips

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

Danielle_1 — Mon, 11 Sep 2023 17:52:55 GMT

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.

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

Thanks in advance.

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

tamerj1 — Mon, 11 Sep 2023 18:08:54 GMT

Hi Danielle_1

please try

IF (

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

"Yes",

"No"

)

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

Danielle_1 — Mon, 11 Sep 2023 18:27:41 GMT

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.

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

tamerj1 — Mon, 11 Sep 2023 18:51:10 GMT

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.

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

Danielle_1 — Tue, 12 Sep 2023 15:59:12 GMT

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.

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

tamerj1 — Wed, 13 Sep 2023 05:32:38 GMT

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"
)

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

Danielle_1 — Mon, 18 Sep 2023 10:51:23 GMT

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

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

Danielle_1 — Mon, 18 Sep 2023 13:08:23 GMT

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

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

tamerj1 — Mon, 18 Sep 2023 16:35:03 GMT

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"
)
)