Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Show values only if they exist in another table

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 )
        )
    )
)

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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-Forum/ba-p/963216

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/1447523