cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imrangaslam
Frequent Visitor

I need count as per the range defined

Hello

I have two tables

Table 1

Start TimeProductCategoryUnit
1/8/2018 8:38CEM I 52.5 RSETINI_AVG29.4
1/12/2018 7:47CEM I 52.5 RSETINI_AVG29.4
1/15/2018 7:49CEM I 52.5 RSETINI_AVG29
1/17/2018 9:46CEM I 52.5 RSETINI_AVG30.7
1/19/2018 10:40CEM I 52.5 RSETINI_AVG23.4
1/22/2018 9:41CEM I 52.5 RSETINI_AVG28.1
1/24/2018 8:51CEM I 52.5 RTZ.600.BU.2W28.2
1/29/2018 9:28CEM I 52.5 RTZ.600.BU.2W26.7

 

Table 2

ProductIDProductCategoryRedMinValRedMaxVal
CEM I 52.5 RSETINI_AVG110170
CEM I 52.5 R1CSA2430
CEM I 52.5 R2CSA3541
CEM I 52.5 R28CSA6066
CEM I 42.5 RSETINI_AVG120180
CEM I 42.5 R1CSA1521

 

I am looking to count the no. of record for Unit coloumn as per the below criteria

Product code: = CEM I 52.5 R

ProductCategory: = SETINI_AVG

Range as defined : 110 and 170

 

So I need to display in card result 6.

 

Any idea how to slove this.

Thanks you for all help

 

1 ACCEPTED SOLUTION

Hi,

I have maintanted the 2 original tables intact.  Your Customer can edit the ranges of Table2.  Please review my solution again.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
imrangaslam
Frequent Visitor

Thank Asihis for your solution.😎

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User III
Super User III

Hi,

I do not understand.  In Table 2, the range is 110 to 170.  There is no figure in the unit column (for Category SETINI_AVG and Product CEM I 52.5 R) whcih is between 110 and 170.  I am confused.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

HI 

Thanks for  your reply.

I writed wrong. Appologize

 

As correct  as per below:

Product code: = CEM I 52.5 R

ProductCategory: = SETINI_AVG

Range as defined : 110 and 170 (range means I need to count all the record which are <= 110 and all the record which are >= 170)

So product CEM I 52.5 R and its category SETINI_AVG and in Unit we have top 6 record as per the our renge.

The count should be 6 so I can display in cart.

 

Thanks

 

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

Many thank for your reply but I this solution will not help me as I can't put range in the detail table as range customer can update the ranges in table 2. So thats way I have sperate table for each product and its ranges. In table 1 we have transaction and need to check range in table.

😟

Hi,

I have maintanted the 2 original tables intact.  Your Customer can edit the ranges of Table2.  Please review my solution again.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors