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

Count rows by visible Product numbers

Hi ,

I am not able to figure this out.

 

I need to get total number of rows by each Product number. The product number , Sales and Code are from 3 different tables.

I need to get the result like in the last column Expected result count. Thanks a lot.

 

Product NumberSalesCode! AAExpected result ! Count
10116229L296
10116223M296
10116210S296
10116223XL296
1011623XS296
10116212XXL296
101162194L946
101162174M946
101162136S946
101162176XL946
101162112XS946
101162147XXL946
11621100120L1206
1162110094M1206
1162110046S1206
1162110098XL1206
1162110017XS1206
1162110062XXL1206
102116037L377
102116030M377
102116013S377
102116030XL377
10211604XS377
102116015XXL377
1021160127xxxs1277
2 ACCEPTED SOLUTIONS
VishalJhaveri
Resolver III
Resolver III

Hi @Puja 

Can you check if the following measure works fine or not and let me know regarding the same.

TestMeasure = 
VAR x =
    SELECTEDVALUE ( 'Table'[Product Number] )
RETURN
    SWITCH (
        TRUE (),
        x <> x, 98989,
        CALCULATE (
            COUNTROWS ( FILTER ( 'Table', 'Table'[Product Number] = x ) ),
            ALL ( 'Table'[! AA] , 'Table'[Code], 'Table'[Sales])
        )
    )


Thank you.
If my answer helps you, please mark it as solution.

View solution in original post

andhiii079845
Super User
Super User

Hi,

some questions: I think to give a good answer we need the data model. Can you please post the related tables for your matrix. Are you sure, that you want to count everytime the total number in every group of 

Product NumberSalesCode! AA

By interpreting this data it seems that "! AA" are not change the count: 7x times 1021160 but two different "! AA" numbers.

102116037L377
102116030M377
102116013S377
102116030XL377
10211604XS377
102116015XXL377
1021160127xxxs1277

So you have to make it more clear. The best thing are some example data.'
In general a simlar problem is discribed here:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measure-for-counting-ignoring-context-filters...
In your case this can work:

Measure = CALCULATE(COUNT('Table'[Product Number]),ALLEXCEPT('Table','Table'[Product Number]))

But you have to change it to your data model / name. 
The result (data from one table in this example)

andhiii079845_0-1677565641481.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
andhiii079845
Super User
Super User

Hi,

some questions: I think to give a good answer we need the data model. Can you please post the related tables for your matrix. Are you sure, that you want to count everytime the total number in every group of 

Product NumberSalesCode! AA

By interpreting this data it seems that "! AA" are not change the count: 7x times 1021160 but two different "! AA" numbers.

102116037L377
102116030M377
102116013S377
102116030XL377
10211604XS377
102116015XXL377
1021160127xxxs1277

So you have to make it more clear. The best thing are some example data.'
In general a simlar problem is discribed here:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measure-for-counting-ignoring-context-filters...
In your case this can work:

Measure = CALCULATE(COUNT('Table'[Product Number]),ALLEXCEPT('Table','Table'[Product Number]))

But you have to change it to your data model / name. 
The result (data from one table in this example)

andhiii079845_0-1677565641481.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




VishalJhaveri
Resolver III
Resolver III

Hi @Puja 

Can you check if the following measure works fine or not and let me know regarding the same.

TestMeasure = 
VAR x =
    SELECTEDVALUE ( 'Table'[Product Number] )
RETURN
    SWITCH (
        TRUE (),
        x <> x, 98989,
        CALCULATE (
            COUNTROWS ( FILTER ( 'Table', 'Table'[Product Number] = x ) ),
            ALL ( 'Table'[! AA] , 'Table'[Code], 'Table'[Sales])
        )
    )


Thank you.
If my answer helps you, please mark it as solution.

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.

Top Solution Authors