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
julienvdc
Helper III
Helper III

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

1 ACCEPTED 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)

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
HarishKM
Impactful Individual
Impactful Individual

@julienvdc You can use this .

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

Hope this will work 

HarishKM
Impactful Individual
Impactful Individual

@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 

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)

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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?

amitchandak
Super User
Super User

@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

 

julienvdc_1-1616408540025.png

 

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.

julienvdc_2-1616408707575.png

 

 

 

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.