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
Anonymous
Not applicable

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
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

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
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

I make a test with data from two tables

1.png

each row = COUNT(sheet1[nno])

distinctcount = DISTINCTCOUNT(sheet1[nno])

Best Regards

Maggie

Anonymous
Not applicable

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

Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Try somehting like this

 

Measure =
COUNTROWS ( SUMMARIZE ( Table1, Table[Col1], Table[Col2] ,.....Other columns in the table visual) )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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?

Hi @Anonymous

 

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

 

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


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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]) ? 

@Anonymous


Could you share your file?

 

I will try to help


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@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. 

@Anonymous

 

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


Regards
Zubair

Please try my custom visuals

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.