Calculated Column to count number of rows in another table using multiple criteria

[Anonymous]

Hi all,

I'm trying to create a calculated column (or measure) that takes the quantity from Table1 if a value exists for Product P and Date P, but take the quantities from Table2 if no such value exists in Table1.

1. I'm trying to use COUNTROWS to calculate whether there exists such value(s) in Table1. but I'm running into problems building this in both a measure and a calculated column. Can someone please help? The two formulas I am using are below:

Calculation[CountrowsTable1] = CALCULATE(COUNTROWS(Table1),USERELATIONSHIP('Calculation'[SKUDate],Table1[SKUDate]))
IfSalesExistInTable1:=CALCULATE(COUNTROWS(Table1), FILTER(Table1, Table1[Date]))

2. Also, I created a SKU-Date Calculated Column in the Tables with Sales Quantities, and then I created a hidden relationship between those sales tables (so that I can perform  CALCULATE( xxx, USERELATIONSHIP(xxx)) between the tables). Is this a bad practice? What is a good way around this?

I have attached the file here for your reference. Thank you!

[Eric_Zhang]

---

@Anonymous wrote:

Hi all,

I'm trying to create a calculated column (or measure) that takes the quantity from Table1 if a value exists for Product P and Date P, but take the quantities from Table2 if no such value exists in Table1.

1. I'm trying to use COUNTROWS to calculate whether there exists such value(s) in Table1. but I'm running into problems building this in both a measure and a calculated column. Can someone please help? The two formulas I am using are below:

Calculation[CountrowsTable1] = CALCULATE(COUNTROWS(Table1),USERELATIONSHIP('Calculation'[SKUDate],Table1[SKUDate]))
IfSalesExistInTable1:=CALCULATE(COUNTROWS(Table1), FILTER(Table1, Table1[Date]))

2. Also, I created a SKU-Date Calculated Column in the Tables with Sales Quantities, and then I created a hidden relationship between those sales tables (so that I can perform  CALCULATE( xxx, USERELATIONSHIP(xxx)) between the tables). Is this a bad practice? What is a good way around this?

I have attached the file here for your reference. Thank you!

---

@Anonymous

You description is not clear for me, even with the attaced file I can't get the full idea. As to the calculated column, I think you may don't have to countrows, just

calculated Column =
VAR value_ = [you dax to get the expected value]
RETURN
    IF (
        ISBLANK ( value_ ),
        [the dax to get values if the expected value doesn't exist],
        value_
    )