DAX for countif based on the info in different table

[RaitisSef]

Hello!

I have situation where I have a historical data about prices for items in one table  and daily prices in second table. Data are scraped from the website.  I need to have DAX expression/measure so I can do similar logic for COUNTIF in Excel ---> Do count in the daily price list how many times that specific URL could be found in the historical table. In both tables I have URL as Text field.

Historical data table name is Sheet1 (yeah, I know- very original) and daily data table name is "Day_Price", in both column name is "URL".

I tried very simple one - "Count_URL = COUNTA(Sheet1[URL])" but it counts it by ShortID values.

[RaitisSef]

Hey!

Thanx for the explanation @amitchandak  but seems that we have lost somewhere.

So I have 2 tables: Sheet1 (around 300K data rows) and Day_Price (at the end of the day around 2K rows). Day_Price is updated all the time during the day and adding new rows as those are getting scraped. In both tables there is column URL which is matching factor between tables (there isn't any IDs, etc what matches between them, only URL). In excel everything would be simple ---> countif and that's that ---> it would return in the "Day_Price" table number of times it has been seen the URL in the "Sheet1", but I have hard time in PowerBi.

When I try to use Your given formula , it doesn't find the last part of the formula after the comma (If I do it in the table "Day_Price". If I do the same in the table "Sheet1", it doesn't offer it after the "=" -----> I don't understand why it doesn't allow to choose necessary fields. My guess is, I need some extra calculate field somewhere as it allows to use them but doesn't allow pure text fields.

I am hoping You can help me out with this, thanx!

All the best,

Raitis

[Ashish_Mathur]

Hi,

Share data in format that can be pasted in an MS Excel file and show the expected result very clearly.

[RaitisSef]

Hey! Here are 2 images how it's in the excel and how should it be wiht countif.

The first image is as main table in which are those clsoe to 300K rows and there is given column URL.

2nd image is Day_Price table in which currently are only 27 data rows as day only starts but iun column G You can see formula and results it should have. I try to replicate the same thing in the PowerBi but can't because it doesn't allow me to use some of the columns based on where I am.

Formula used in the column G is: "=COUNTIF('[Kopeja tabula.xlsx]Sheet1'!$C:$C,A2)" ----> A2 in this case would be in the table "Day_Price"

[Ashish_Mathur]

Hi,

Write this calculated column formula in the Day price table

Countif = calculate(countrows(Table1),filter(Table1,Table1[URL]=earlier('Day price'[URL])))

Hope this helps.

[amitchandak]

@RaitisSef , not very clear

You can have column across table

New column in sheet 2 =

COUNTX(filter( Sheet1, sheet1[ID] = sheet2[ID]), Sheet1[URL])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8