Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey everyone,
Appologies if my question title isn't clear, I'll try to clarify my issue here 🙂
I have this site where users can ask questions, post articles and ideas. Any of these posts can be tagged with several topics.
I a trying to measure/count the amount of articles that have been posted on my sites which have been tagged with a specific topic (call it Topic XYZ), and display that number in a few visuals (pie, card). Then using a slicer I would like to be able to apply (additional) filters allowing me to drill down into the topics that these articles have been tagged with (on top of Topic XYZ) .
I am trying to build measures using filters but once I apply those filters it basically take out all the other topcis from my table which prevents me from filtering further...
Here are the tables that I have:
Action table
action_type | node_id | blablabla | blablabla |
question | 100 | ... | ... |
answer | 101 | ... | ... |
newArticle | 102 | ... | ... |
newArticle | 103 | ... | ... |
comment | 104 | ... | ... |
newArticle | 105 | ... | ... |
newArticle | 106 | ... | ... |
Topics x Nodes table
node_id | topic_id |
100 | XYZ |
100 | AAA |
102 | XYZ |
103 | BBB |
103 | AAA |
105 | XYZ |
105 | AAA |
105 | BBB |
106 | XYZ |
106 | BBB |
Then I have a reference table where the topic_id match their actual display name.
So essentially what I am trying to do is:
1/ Count all newArticles which have been tagged with XYZ
->> this should return node_id: 102, 105, 106 ->> 3
2/ Then from this subset, I want to be able to use a visual slicer to filter further.
->> For instance, how many have been tagged with BBB? ->> 105, 106 ->> 2
Explained like this now feels more simple, but I haven't been able to find the right formula that would allow me to calculate step 1 while not removing the lines of my topics table where XYZ does not appear. Maybe I shouldn't use filters...
Any thoughts?
Let me know 🙂
julien
Solved! Go to Solution.
Hi @julienvdc,
The selectedvalue function is used to extract the current row content. If this expression calculates on a single row, it will extract the current value. (BTW, the function include the processing to processing multiple row contents, if you do not add processings, it will return blank when you work on aggregate row contents)
Using the SELECTEDVALUE function in DAX
BTW, if you want to keep other filter effects that apply to the target table, you can consider using allselected function to package the parameters table.
The definitive guide to ALLSELECTED
Regards,
Xiaoxin Sheng
@julienvdc You can use this .
YourMeasurename = calculate(countrow(Topics), Action[action_type] ="newArticle")
Hope this will work
@julienvdc Bruh , Try This .
Hehe bruh, thanks bruh. I am not sure what this is doing though... I am just filtering when the data equals itself? 🤔
Hi @julienvdc,
The selectedvalue function is used to extract the current row content. If this expression calculates on a single row, it will extract the current value. (BTW, the function include the processing to processing multiple row contents, if you do not add processings, it will return blank when you work on aggregate row contents)
Using the SELECTEDVALUE function in DAX
BTW, if you want to keep other filter effects that apply to the target table, you can consider using allselected function to package the parameters table.
The definitive guide to ALLSELECTED
Regards,
Xiaoxin Sheng
Hey @HarishKM , thanks for your suggestion 🙂
I think it is an alternative to @amitchandak's.
I am still struggling to pre-filter on a specific via the measure or the visual to. As explained, these post can be tagged with multiple topics, and the idea is to retain only posts with a specific topic AND any other topics it was tagged with... then I could use the slice to filter over the "any other topics" part. I don't know if that makes sense?
Are there other elements (that I didn't mention) that could influence this?
@julienvdc , Not very clear. But you can try a measure like
for Question 1
calculate(count(Topics[topic_id]), filter(Action, Action[action_type] ="newArticle"))
Hey @amitchandak , thanks for the swift reply! 🌟
Sorry that my message wasn't clear...
With your suggested measure, I do get the amount of articles posted along with any topics. Then using the topics slicer I am able to filter this count to view only articles that have been posted with the seleced topic(s).
But I am still facing issues to force-in a "pre-filter". As described in my example I would like to get the number of all articles that have been tagged with that XYZ topic, then after that be able to use the slicer over those.
Here is what I am doing, I used your measure in the card visual, and added a visual filter that would only show me the count of articles posted with topic id = 153
But then I when I start using the slicer to filter on top of it, it displays blank values... though I've checked, articles with the combination of those topics exists.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |