Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PDS001
Regular Visitor

Reference different tables when creating a measure

I am trying to create a Measure within a table called 'All Sales'. The Measure should calculate the ratio of times "1" appears in the [Sales] column in the 'Sales Numbers' table, to the number of records (ie rows) in the 'All Sales' table. I dont want to copy the Sales column into the All Sales table as to keep the data files small - therefore using Measure and not adding a column. The issue is when using Countrows or If statments it won't let me reference any column outside the 'All Sales' table.

 

The formula I thought (along with may other versions reads like:

Measure = DIVIDE((COUNTROWS('Sales'['Sales Numbers'], == "1")), COUNTROWS('All Sales'))
 
 'Sales Number' Table
RecordIDSales
11
23
35
41

 

'All Sales' Table

RecordIDSold
17
24
39
43
58

 

So the resulting Measure should = 2/5 = 2.5

Thanks for the help as I know there has to be an easy way

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Create a single column table called Records with only unique Record ID in that table.  Create a relationship (Mnay to One and Single) from the RecordID column of the 2 data tables to the RecordID column of the new table.  Write these measures and drag the last one to a card visual

Measure1 = calculate(COUNTROWS('sales'),'Sales'['Sales Numbers'], == "1")

Measure2 = COUNTROWS('All Sales')

measure3 = divide([Measure1],[Measure2])

Hope this helps.


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

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Create a single column table called Records with only unique Record ID in that table.  Create a relationship (Mnay to One and Single) from the RecordID column of the 2 data tables to the RecordID column of the new table.  Write these measures and drag the last one to a card visual

Measure1 = calculate(COUNTROWS('sales'),'Sales'['Sales Numbers'], == "1")

Measure2 = COUNTROWS('All Sales')

measure3 = divide([Measure1],[Measure2])

Hope this helps.


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

Read about CALCULATE()

OK I think with the above suggestions and correcting the syntax I've worked it out.

 

As Ahsish Mathur says you can create two seperate Measures, then divide those. This makes it easier to follow. Originally I wanted to avoid this to minimise processing time of 3 measures when loading in millions of row data situations, and creating more items in the Fields section.

 

So combining this into one equation reads:

 

Measure = DIVIDE(COUNTROWS('All Sales'), CALCULATE(COUNTROWS('Sales'),'Sales'[Sales Numbers] == "1"))
 
So the key point for others if I am correct:
COUNTROWS only counts the rows in a Table not a column, so you can only reference tables.
To find the quantity of specific values in a column, you filter the table using CALCULATE then count the rows remaining.
So:
CALCULATE(COUNTROWS('Sales'),'Sales'[Sales Numbers] == "1"
reads as:
Count the rows left in the Sales table after the filter Sales Numbers = 1. Where the term CALCULATE creates the filter , not COUNTROWS.
 
Interesting thought though. Would using the 3 measures method be just as quick to load as the 1 measure method?

Use DAX Studio to examine the query plans.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors