Calculations using 2 datasets(tables)

[BSM1985]

Hi Team,

I've 2 tables (datasets) as shown below and I would like to have the calculation from the 2 datasets as below:

[v-janeyg-msft · Accepted solution]

Hi, @BSM1985 

It’s my pleasure to answer for you.

If you just want to group by columnA, and add up to get the number that meets the two conditions (columnA=column1 and columnc="Q"),

Like this:

You can try it(First, create the relationship between the two tables):

Measure:

Qnumbers =
COUNTROWS (
    FILTER (
        DATASET1,
        AND (
            DATASET1[COLUMNA] = RELATED ( DATASET2[COLUMN1] ),
            DATASET1[COLUMNC] = "Q"
        )
    )
)

If it doesn’t meet your requirements, could you please share what your expected results and share some sample data ?

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@BSM1985 ,

Column in Dataset 1

Sumx( filter([Data Set2], [Data Set1].[ColumnA] =[Data Set2].[ColumnA] && [Data Set1].[ColumnC] ="Q" ), [ColumnA])

A column in Dataset 2

Sumx( filter([Data Set1], [Data Set1].[ColumnA] =[Data Set2].[Column1] && [Data Set1].[ColumnC] ="Q" ), [Column1])

Measure

Sumx( filter([Data Set1], [Data Set1].[ColumnA] = max([Data Set2].[Column1]) && [Data Set1].[ColumnC] ="Q" ), [Column1])

[BSM1985]

Hi @amitchandak ,

Sorry for repeated questions on this, I see the below error message when I implemented the first calculation

Sumx( filter([Data Set2], [Data Set1].[ColumnA] =[Data Set2].[ColumnA] && [Data Set1].[ColumnC] ="Q" ), [ColumnA])

"The function SUMX cannot work with typs of values string"

My Coulmn A is of string type. Could you advice?

[BSM1985]

Hello @amitchandak ,

Thanks for your time as always.

I also have the OVER PARTITIO BY clause as higlighted below..kindly guide me how to fit this in your calculation

SUM
(
IF [Dataset1].[COLUMN A]=[Dataset2].[COLUMN 1] AND [Dataset1].[COLUMN C]="Q"

THEN1 ELSE 0 END

)
OVER PARTITION BY [Dataset1].[COLUMN A]