Remove row values where one of the metrics have null

[ignas]

Hey there

I cannot find a solution to my problem. I hope there is somebody who can help me.

I have two fact tables that I cannot combine into one because in the real data model it is just not possible.

Sales and Cost tables are fact table and are linked through Month and Country dim tables

Country A for 11-2019 does not have Cost and Country B does not have Sales for 12-2019.

How can I implement a page filter where we only display values where there are Sales and Cost? So in this example, we should only have:

Here is the link to PBIX File 

Thanks for the help

[v-juanli-msft · Accepted solution]

Hi @ignas 

Create measures

Measure flag = IF(SUM(Fact_sales[Sales])<>BLANK()&&SUM(Fact_cost[Cost])<>BLANK(),1,0)

Measure sales = CALCULATE(SUM(Fact_sales[Sales]),FILTER(Fact_cost,[Measure flag]=1))

Measure cost = CALCULATE(SUM(Fact_cost[Cost]),FILTER(Fact_cost,[Measure flag]=1))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @ignas 

Create measures

Measure flag = IF(SUM(Fact_sales[Sales])<>BLANK()&&SUM(Fact_cost[Cost])<>BLANK(),1,0)

Measure sales = CALCULATE(SUM(Fact_sales[Sales]),FILTER(Fact_cost,[Measure flag]=1))

Measure cost = CALCULATE(SUM(Fact_cost[Cost]),FILTER(Fact_cost,[Measure flag]=1))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ignas]

@v-juanli-msft 
You helped me the last time to find a solution. I was just trying to find another solution and I am very close. I use SUMX, but I get wrong Total value:

File can be found here:
Power BI File 

[ImkeF · Accepted solution]

Hi @ignas 

you need to wrap a CALCULATE around the sum to transfer the row context from the SUMX into a filter context:

IF = SUMX(dimCustomer,
        IF(CALCULATE(sum(fact_open[amount]))=BLANK(),BLANK(),[Sum Revenue]))

[ignas]

@ImkeF You are absolutely amazing. It indeed works. Thank you very much. I am just completely confused what in this case "calculate" does. If I remove calculate then I get the wrong answer.
I always use calculate then I need to filter something: Calculate(sum(metrics); dim=x)

But you just wrapped sum with "calculate". So in my mind, it should not be a difference with calculate and without. I would highly appreciate if you could elaborate on this.

[ImkeF]

Hi @ignas 

this article has a good explanation for it: https://www.sqlbi.com/articles/understanding-context-transition/ 

[ImkeF]

Hi @ignas 

you need a measure that checks for blanks in the other table and then return blank itself like so:

Measure = if(ISBLANK(Sum(Fact_cost[Cost])),BLANK(), SUM(Fact_sales[Sales]))

[ignas]

@ImkeF 

Your measure will only work in the table, but not in the Card as shown above. With your formula, I get the same results.
Ideally, I need a filter where I could filter the whole page. Any ideas if it is possible?