Show values only if they exist in another table

[Slowik131]

Hi,

I have tables that when simplified look like this:

Table A

Table B

Essentially what I want to end up is, is a measure that would sum up the tarif_kpi column from Table B only if the values for ID's exist in the Table A.

So in this simplified case, to sum up tarif_kpi where package_id in [1,2,3] and comm_id in [1,2].

I tried to achieve it with TREATAS() like that:

tarif treatas = VAR sales = SUMMARIZE('Sales', 'Sales'[Date], 'Sales'[Package_ID], 'Sales'[Comm_ID])

VAR filtered = TREATAS(sales, 'Calendar'[Dates], Packages[ID Package], 'Commercial Organisation'[ID Org Com])

RETURN
CALCULATE(SUM(ARPU[tarif_kpi]), KEEPFILTERS(filtered))

Unfortunately it doesn't work correctly.

Do you have any idea how to make it work?

Thanks!

[lbendlin]

TREATAS is a good approach.  Remember you want to filter by the Comm_ID in Table A

Measure = CALCULATE(something from Table B,TREATAS(VALUES(TableA[Comm_ID]),TableB[Comm_ID]))

[Slowik131 · Accepted solution]

So something like this?

tarif treatas 2 = VAR sales = SUMMARIZE('Sales', 'Sales'[Date], 'Sales'[Package_ID], 'Sales'[Comm_ID])

VAR filtered = TREATAS(sales, 'ARPU'[Date], ARPU[ID Package], 'ARPU'[ID Org Com])

RETURN
CALCULATE(SUM(ARPU[tarif_kpi]), KEEPFILTERS(filtered))​

It still didn't work as shown below, though it might be due to the compelxicity of the model:

However, I did find a non elegant solution that works:

tarif treatas = VAR sales = SUMMARIZE('Sales', 'Sales'[Date], 'Sales'[Package_ID], 'Sales'[Comm_ID])

VAR filtered = TREATAS(sales, 'Calendar'[Dates], Packages[ID Package], 'Commercial Organisation'[ID Org Com])

RETURN
SUMX (
    VALUES ( 'Commercial Organisation'[ID Org Com] ),
    CALCULATE (
        CALCULATE (
            SUM ( ARPU[TARIF_LOCAL] ),
            KEEPFILTERS ( filtered ),
            FILTER ( 'Commercial Organisation', [Sales] > 0 ),
            FILTER ( Packages, [Sales] > 0 )
        )
    )
)

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...