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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Michele_I
Frequent Visitor

find related rows in a table

Hi all,

i have a table like this (hundreds of records) named projects-tags:

ProjectTAG
project 1tag A
project 1tag B
project 1tag C
project 2tag A
project 2tag D
project 3tag n...

 

So, i have many projects, and each of them has many tags.

(I also have a projects-table and a tags-table, and the table posted above is linked to both of them with many:one relation and two-ways filters direction).

 

Question is: which other tags are associated with projects associated to tag A? and which is their occurrency? Then with tag B and so on.

In other words, i want to choose a tag (eg. with a slicer in a report) and find the list other tags connected to it and their intensity (n. of occurrencies).

 

I tried creating this calculated table:

 

 

NewTable = 
var selectedtag = SELECTEDVALUE('Tags-table'[Label tag])
return
CALCULATETABLE(projects-tags; 'projects-tags'[Label tag] <> selectedtag)

 

 

And then linked this new table to projects table with many:one relation and two-ways filter direction. Problem is the tag selected (SELECTEDVALUE) is still in the list of the new table.

 

How can i do?

thank you very much

 

Michele

1 ACCEPTED SOLUTION

Hi,

 

I am sorry not to share my test pbix file with you.

After viewing your file, i find the problem is that it is due to the relationship between two tables.

You can remove this relationship, or if you do not want to remove it, please take following steps:

1)Create a new slicer table:

SlicerTable = DISTINCT(SELECTCOLUMNS('Projects-tags',"Label tag",'Projects-tags'[Label tag]))

2)Change the original measure to this:

Control = 
IF (
    SELECTEDVALUE ( 'Projects-tags'[Label tag] )
        IN SELECTCOLUMNS (
            FILTER (
                ALLSELECTED ( 'projects-tags' ),
                [Project]
                    IN SELECTCOLUMNS (
                        FILTER (
                            ALLSELECTED ( 'projects-tags' ),
                            'projects-tags'[Label tag] IN FILTERS ( 'SlicerTable'[Label tag] )
                        ),
                        "P", [Project]
                    )
            ),
            "T", [Label tag]
        )
        && NOT (
            SELECTEDVALUE ( 'Projects-tags'[Label tag] )
                IN FILTERS ( 'SlicerTable'[Label tag] )
        ),
    1,
    -1
)

When you choose slicer, it shows:

33.PNG

Here is your pbix file changed by me:

pbix 

 

Best Regards,

Giotto Zhi

 

 

View solution in original post

8 REPLIES 8
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try to use SelectColumns function to extract the columns you want as a table, like this:

Table = SELECTCOLUMNS('NewTable',"project",'NewTable'[Project])

 Hope this helps.

 

Best Regards,

Giotto Zhi

hi @v-gizhi-msft, thanks for your reply.

 

With your suggestion i can get the list of the projects, but  this is not what i was looking for.

 

I'm trying to find the tags "associated" (through the projects) to the tag I choose.

e.g. in the table of my first post, if I choose tag A (e.g. with a slicer) the output should be a list including tags B and C (as linked to A through project 1) and tag D (as linked to A through project 2). After achieving this result i can create a measure to count the occurencies (in this example always 1).

Hi,

 

Please try to create this measure:

Control =
IF (
    SELECTEDVALUE ( 'projects-tags'[Label tag] )
        IN SELECTCOLUMNS (
            FILTER (
                ALLSELECTED ( 'projects-tags' ),
                [Project]
                    IN SELECTCOLUMNS (
                        FILTER (
                            ALLSELECTED ( 'projects-tags' ),
                            'projects-tags'[Label tag] IN FILTERS ( 'Tags-table'[Label tag] )
                        ),
                        "P", [Project]
                    )
            ),
            "T", [Label tag]
        )
        && NOT (
            SELECTEDVALUE ( 'projects-tags'[Label tag] )
                IN FILTERS ( 'Tags-table'[Label tag] )
        ),
    1,
    -1
)

Then choose 'projects-tags'[label tag] as a table visual and apply this measure to this visual.

When you choose one tag in slicer, it shows:

81.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

 

Hi @v-gizhi-msft 

thank you very much: i really appreciate your help!

I have to admit i didn't understand how exactly the formula works... anyway, I applied it and i get no errors, but  it's  not working properly as the output table (the one in your screenshot) is always blank.

This seems strange to me as in your screenshot it looks working perfectly.

Here is my test file where i replicated exactly what you did: https://1drv.ms/u/s!AtoXT-bg-4B34girsAUWUXp2MPIs?e=9SRYBF

... please, where am I doing wrong?

 

thank you so much

Michele

Hi,

 

I am sorry not to share my test pbix file with you.

After viewing your file, i find the problem is that it is due to the relationship between two tables.

You can remove this relationship, or if you do not want to remove it, please take following steps:

1)Create a new slicer table:

SlicerTable = DISTINCT(SELECTCOLUMNS('Projects-tags',"Label tag",'Projects-tags'[Label tag]))

2)Change the original measure to this:

Control = 
IF (
    SELECTEDVALUE ( 'Projects-tags'[Label tag] )
        IN SELECTCOLUMNS (
            FILTER (
                ALLSELECTED ( 'projects-tags' ),
                [Project]
                    IN SELECTCOLUMNS (
                        FILTER (
                            ALLSELECTED ( 'projects-tags' ),
                            'projects-tags'[Label tag] IN FILTERS ( 'SlicerTable'[Label tag] )
                        ),
                        "P", [Project]
                    )
            ),
            "T", [Label tag]
        )
        && NOT (
            SELECTEDVALUE ( 'Projects-tags'[Label tag] )
                IN FILTERS ( 'SlicerTable'[Label tag] )
        ),
    1,
    -1
)

When you choose slicer, it shows:

33.PNG

Here is your pbix file changed by me:

pbix 

 

Best Regards,

Giotto Zhi

 

 

Hi @v-gizhi-msft 

and thank you very much.

Sorry for replying late, i was away last days.

 

Now it works perfectly!

Thanks again for your help

 

Michele

amitchandak
Super User
Super User

Try. Not tested

NewTable = 
var selectedtag = maxx('Tags-table',SELECTEDVALUE('Tags-table'[Label tag])) //optional 
return
CALCULATETABLE(all(projects-tags); 'projects-tags'[Label tag] <> selectedtag)

@amitchandak thank yu for your suggestion.

Unfortunately, i get the same output i was getting before with the code posted in my first message...

the TAG i choose in the slicer (in tags-table) is still there in the list of the calculated table, despite the filter (<> selectedtag).

any other idea?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.