Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
i have a table like this (hundreds of records) named projects-tags:
Project | TAG |
project 1 | tag A |
project 1 | tag B |
project 1 | tag C |
project 2 | tag A |
project 2 | tag D |
project 3 | tag 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
Solved! Go to 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:
Here is your pbix file changed by me:
Best Regards,
Giotto Zhi
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:
Hope this helps.
Best Regards,
Giotto Zhi
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:
Here is your pbix file changed by me:
Best Regards,
Giotto Zhi
and thank you very much.
Sorry for replying late, i was away last days.
Now it works perfectly!
Thanks again for your help
Michele
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?
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |