Count if multiple conditions in different tables

[adelaidiana]

Hi all, I am totally new on this jurney and I need to do a report and calculate how many rows have data based on other 3 conditions based on diferent tables. I need to create the report in Table 3. Ex: If Table 1 (Asset Eq=Cars and Model number=x), filter Table 2 Asset Equipment column for Cars and count under Model Number column how many entries we have. This number has to appear in Table 3 under Model Number /Cars.  Table 3 is a duplication of Table 1, just when we have x, we need the numbers of cells with data from Table 2. I have already completed Table 3 with the results we should obtain from the report.

Thank you so much in advance for all your help.

Asset Equipment	Model number	Manufacturer	Start-up date
Cars	x	x	x
Bicycles	x		x
Scouters			x

Asset Equipment	Equipment	Model number	Manufacturer	Start-up date
Cars	20015234	Model 1	a	01/01/1980
Bicycles	20015235	Model 2	b	01/01/1980
Cars	20015236	Model 3	x	01/01/1980
Scouters	20015247	Model 4	x
Bicycles	20015271	Model 5		30/03/2007
Scouters	20015398	Model 6		01/01/1992
Cars	20015403	Model 7	b
Bicycles	20015702	Model 8		01/01/1987
Scouters	20015703
Cars	20015746	Model 10	x	01/01/1980
Scouters	20015772	Model 11		02/01/1980
Bicycles	20015773	Model 12	x
Cars	20015774	Model 13	b	04/01/1980
Scouters	20015775	Model 14		05/01/1980
Scouters	20015779			06/01/1980
Bicycles	20015840	Model 16		07/01/1980
Scouters	20015877	Model 17	x	08/01/1980
Cars	20015879	Model 18
Cars	20015885	Model 19	b	10/01/1980
Scouters	20015886	Model 20		11/01/1980
Bicycles	20015946	Model 21		12/01/1980
Cars	20015947			13/01/1980
Cars	20015948	Model 23
Bicycles	20015949	Model 24	b	15/01/1980
Cars	20015964	Model 25		16/01/1980

Asset Equipment	Model number	Manufacturer	Start-up date
Cars	9	6	7
Bicycles	7		6
Scouters			6

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table DAX formula =
VAR _t01 =
    SELECTCOLUMNS (
        Table1,
        "@assetequipment", Table1[Asset Equipment],
        "@modelnumber", IF ( Table1[Model number] <> BLANK (), 1 ),
        "@manufacturer", IF ( Table1[Manufacturer] <> BLANK (), 1 ),
        "@startupdate", IF ( Table1[Start-up date] <> BLANK (), 1 )
    )
VAR _t02 =
    SELECTCOLUMNS (
        Table2,
        "@assetequipment", Table2[Asset Equipment],
        "@modelnumber", IF ( Table2[Model number] <> BLANK (), 1 ),
        "@manufacturer", IF ( Table2[Manufacturer] <> BLANK (), 1 ),
        "@startupdate", IF ( Table2[Start-up date] <> BLANK (), 1 )
    )
VAR _result =
    ADDCOLUMNS (
        _t01,
        "ModelNumber",
            IF (
                [@modelnumber] = 1,
                SUMX (
                    FILTER ( _t02, [@assetequipment] = EARLIER ( [@assetequipment] ) ),
                    [@modelnumber]
                )
            ),
        "Manufacturer",
            IF (
                [@manufacturer] = 1,
                SUMX (
                    FILTER ( _t02, [@assetequipment] = EARLIER ( [@assetequipment] ) ),
                    [@manufacturer]
                )
            ),
        "Startupdate",
            IF (
                [@startupdate] = 1,
                SUMX (
                    FILTER ( _t02, [@assetequipment] = EARLIER ( [@assetequipment] ) ),
                    [@startupdate]
                )
            )
    )
RETURN
    SUMMARIZE (
        _result,
        [@assetequipment],
        [ModelNumber],
        [Manufacturer],
        [Startupdate]
    )

[adelaidiana]

Thank you so much Kim. 🤗

Diana