Forum Discussion

julienvdc's avatar
julienvdc
Helper III
5 years ago
Solved

Count rows based on data from other table while not filtering out that other table

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_typenode_idblablablablablabla
question100......
answer101......
newArticle102......
newArticle103......
comment104......
newArticle105......
newArticle106......

 

 

Topics x Nodes table

node_idtopic_id
100XYZ
100AAA
102XYZ
103BBB
103AAA
105XYZ
105AAA
105BBB
106XYZ
106BBB

 

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

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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)

    SELECTEDVALUE 

    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

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • julienvdc's avatar
      julienvdc
      Helper III
      5 years ago

      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.

       

       

       

  • HarishKM's avatar
    HarishKM
    Super User
    5 years ago

    julienvdc You can use this .

    YourMeasurename = calculate(countrow(Topics), Action[action_type] ="newArticle")

    Hope this will work 

    • HarishKM's avatar
      HarishKM
      Super User
      5 years ago

      julienvdc  Bruh , Try This .

       

      Test sales = CALCULATE(COUNTROWS(data),FILTER(data,data[Discount]=SELECTEDVALUE(data[Discount])))

       

      You can take inspiration from this Above dax . just replace column with your coloumn name like topic_ID.

      Hope this will help 
      • julienvdc's avatar
        julienvdc
        Helper III
        5 years ago

        Hehe bruh, thanks bruh. I am not sure what this is doing though... I am just filtering when the data equals itself? ğŸ¤”

    • julienvdc's avatar
      julienvdc
      Helper III
      5 years ago

      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?