cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hwang
Regular Visitor

DAX - How to Count Rows of a table column which has some visualization filter

Dear Experts,

 

I have no access to Modelling tab, only access for BI report & I wonder how can i count each rows in below table which has been filtered (visualization filter). 

Invpart table.jpg

>> I need to count number of row of Location No, for below Inventory table. 

>> Result required is another column beside Date, with count 1 for each rows.

>> and obviously the total count. 

>> *Each of the column has filter which is a selected filter outside the visual table (for user to selected in the dashboard).   

 

Let me know if you have any idea for the DAX measure. Thanks in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: DAX - How to Count Rows of a table column which has some visualization filter

Hi @Hwang

I make a test with data from two tables

1.png

each row = COUNT(sheet1[nno])

distinctcount = DISTINCTCOUNT(sheet1[nno])

Best Regards

Maggie

View solution in original post

9 REPLIES 9
Super User III
Super User III

Re: DAX - How to Count Rows of a table column which has some visualization filter

@Hwang

 

Try somehting like this

 

Measure =
COUNTROWS ( SUMMARIZE ( Table1, Table[Col1], Table[Col2] ,.....Other columns in the table visual) )
Try my new Power BI game Cross the River
Hwang
Regular Visitor

Re: DAX - How to Count Rows of a table column which has some visualization filter

Hi Zubair,

 

Thanks for your advice, but the column are from multiple tables, but each has a relation.

How can we write the DAX formula, if it is from different tables?

Super User III
Super User III

Re: DAX - How to Count Rows of a table column which has some visualization filter

Hi @Hwang

 

Start with the Many side Table.......

 

Then Summarize will allow you to add column from the one side table

Try my new Power BI game Cross the River
Hwang
Regular Visitor

Re: DAX - How to Count Rows of a table column which has some visualization filter

Hi @Zubair_Muhammad

 

Sorry, i'm a noob in DAX codding, can you help to elaborate on the DAX codding?

 

or can I write it as:

Measure = COUNTROWS ( SUMMARIZE ( Table1, Table1[Col1]), SUMMARIZE (Table2, Table2[Col1]) ? 

Super User III
Super User III

Re: DAX - How to Count Rows of a table column which has some visualization filter

@Hwang


Could you share your file?

 

I will try to help

Try my new Power BI game Cross the River
Hwang
Regular Visitor

Re: DAX - How to Count Rows of a table column which has some visualization filter

@Zubair_Muhammad I would like to, but apparently the file is prohibited by our company IT to be sent out, since it's link into the cube of the database.

 

Or do you mind to elaborate the formula for the summarize, let say i have 2 table A & B  each table has 1 column  use as the filtered, and i gotta get the count rows from it. 

Highlighted
Super User III
Super User III

Re: DAX - How to Count Rows of a table column which has some visualization filter

@Hwang

 

Sure.

 

If you have two Tables A and B
Lets say A is many side and B is one side

 

Then you can write a formula

 

Summarize(manysidetable,Columnsfrommany side Table, column from one side Table....)

Summarize(TableA,TableA[Col1],TableB[Col2] etc)


The first argument of Summarize is a Table...It should be the many side table

Try my new Power BI game Cross the River
Community Support
Community Support

Re: DAX - How to Count Rows of a table column which has some visualization filter

Hi @Hwang

I make a test with data from two tables

1.png

each row = COUNT(sheet1[nno])

distinctcount = DISTINCTCOUNT(sheet1[nno])

Best Regards

Maggie

View solution in original post

Hwang
Regular Visitor

Re: DAX - How to Count Rows of a table column which has some visualization filter

@v-juanli-msft thanks Maggie, that's really help.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors