Issue with measure as a filter

[Bimbone]

Hi all,

I have some struggles to set this correctly, any help would be appreciated.

I have a Table Visual, containing several fields, all from the same table "CoppeCitta". One field is "SFPair", which can have several values (eg: 0,1,2...). I'd like to filter such visual by dynamically choosing only one value of SFPair.

That value would be dependent on three slicers existing in that page, one of which is filtering the table "CoppeCitta", while the two others are filtering other related tables.

So I created a measure to achieve that, with the intention to then make a second measure to compare with the field. However, I cannot make that first measure properly working (basically, it's not returning a unique SFPair value) - here is the DAX:

CorrespondingMatchSF =

    VAR SelectedCitta = SELECTEDVALUE(Cities[ID_City])

    VAR SelectedYR = SELECTEDVALUE(Cities_numbers[YR])

    VAR selectedComp = selectedvalue(CoppeCitta[Competizione])

    RETURN

    CALCULATE(

        FIRSTNONBLANK('CoppeCitta'[SFPair],1),      FILTER(

            'CoppeCitta',            

            related(Cities[ID_City])=SelectedCitta&&

            RELATED(Cities_numbers[YR])=SelectedYR&&

            CoppeCitta[Competizione]=selectedComp

        )

    )

You can see how it's referring to the three slicers.

FYI, this would be the second measure for the final check:

CheckSF = IF(VALUES(CoppeCitta[SFPair])=[CorrespondingMatchSF],1,0)

However, it is returning always 1 because the first measure is not giving a unique value, but the same value as SFPair for every row.

Thank you in advance!

[Anonymous]

Hello,Fowmy ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@Bimbone .I am glad to help you.
I noticed  your original code :

CALCULATE(

        FIRSTNONBLANK('CoppeCitta'[SFPair],1),      FILTER(

            'CoppeCitta',           

            related(Cities[ID_City])=SelectedCitta&&

            RELATED(Cities_numbers[YR])=SelectedYR&&

            CoppeCitta[Competizione]=selectedComp

        )

    )
The table here does not perform the operation of removing external filtering conditions.
The FILTER function considers external filtering conditions in the context of its calculations, which can result in the returned value being influenced by the current row. To avoid this, you can use the ALL function to remove external filtering conditions, thus ensuring that the FILTER function only considers the conditions you specify.
You can try modifying the table using ALL()/ALLSELECTED()/ALLEXCEPT().
You could try the following modifications

CorrespondingMatchSF =
    VAR SelectedCitta = SELECTEDVALUE(Cities[ID_City])
    VAR SelectedYR = SELECTEDVALUE(Cities_numbers[YR])
    VAR selectedComp = SELECTEDVALUE(CoppeCitta[Competizione])
    RETURN
    CALCULATE(
        SELECTEDVALUE('CoppeCitta'[SFPair]), 
        FILTER(
            ALL('CoppeCitta'),            
            RELATED(Cities[ID_City]) = SelectedCitta &&
            RELATED(Cities_numbers[YR]) = SelectedYR &&
            CoppeCitta[Competizione] = selectedComp
        )
    )

Otherwise, as you currently write it, there is a good chance that the internal data of the measure being calculated will be affected by the current row of the table visual itself, which will affect the final result.
Double-checking your real computing environment is, I think, the key to solving your problem.

For the SELECTEDVALUE() function, which returns null when multiple values are selected for the same slicer field, you might want to try using an aggregate function such as MAX/MIN instead of just SELECTEDVALUE!

Regarding the link to the pbix file you provided subsequently, unfortunately I can't access it due to environmental constraints. You might try using the unencrypted OneDrive /GitHub link to analyze your test data.

You can also additionally provide some test data simulating your real environment and screenshots of what to expect .

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bimbone]

Hi @Anonymous  & @Fowmy 

I could now test your solution, but unfortunately I still get the same results. As I cannot publish my files in any other environments than Google Drive, let me restart my explanation from scratch, giving you some samples as well.

Now, all the information are in the same table "CoppeCitta", you can see below a sample:

Then, I have all the visuals in the below page:

You can see the three slicers: "Competizione" on the left, "Scegli anno" in the middle (filtering the year in the column "Anno") and the cities on the right. This last slicer is actually filtering another linked table, but it gives the same value as the column "CittaMain".

If you look at the table in the centre bottom, what I want to achieve is that instead of showing all the records, it gets filtered on the "SFPair" which corresponds to the combination of the three slicers above.

Any further feedback or suggestion would be appreciated. Thanks!

[Bimbone]

Hi Carson,

thank you for your reply.

I am now travelling for a few days, but when I am back next week I will test your solution and will let you know!

[Fowmy]

@Bimbone 

Use Max,

CorrespondingMatchSF =  
VAR SelectedCitta =
    SELECTEDVALUE ( Cities[ID_City] )
VAR SelectedYR =
    SELECTEDVALUE ( Cities_numbers[YR] )
VAR SelectedComp =
    SELECTEDVALUE ( CoppeCitta[Competizione] )
RETURN
    CALCULATE (
        MAX ( 'CoppeCitta'[SFPair] ),
        'CoppeCitta',
        RELATED ( Cities[ID_City] ) = SelectedCitta,
        RELATED ( Cities_numbers[YR] ) = SelectedYR,
        CoppeCitta[Competizione] = SelectedComp
    )

If you have proper relationships defined in your model, why you the following meaure would not get you the expected results:

CorrespondingMatchSF =  MAX ( 'CoppeCitta'[SFPair] )

[Bimbone]

Hi, thank you for your reply.

Actually, when I write your formula (so without having an explicit "FILTER"), I now get the error "The column 'Cities[ID_City]' either doesn't exist or doesn't have a relationship to any table available in the current context.".

It is true that the table "Cities" is not related directly to the table "CoppeCitta", but there is another table ("Country_numbers") in the middle .

The overall relationship is Cities -> Country_numbers (1-to-many)  and then Country_Numbers -> CoppeCitte (1-to-many).

Because "CoppeCitta" is a table built in PowerQuery merging other tables, shall I try to add the field I need from "Cities" directly in CoppeCitta as a calculated column?

[Fowmy]

@Bimbone 

Add some dummy data to your model and share the PBIX file explaining the expected results.
Save the file in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

[Bimbone]

Hi, thank you, here it is

https://drive.google.com/file/d/1HPZrCDdwVpwMSZxW9ORgK8cqgpxxdgwb/view?usp=sharing

If you check in the tab "Coppe citta", the visual table called "Semifinali" should get filtered on SFPair (you can add the measures "CorrespondingMatchSF" and "CheckSF").

If you have anyway any other general comments on the model, please do not hesitate