Count rows while filtering with an inactive relationship

[DotU]

Hi guys,

I have two tables linked with an inactive relationship.
Table 1 "Required Files" is the One side with unique values, column name: "Register Code & Mth-Yr"
Tabe 2 "Submitted Files" is the Many side with column "Register Code & Run Date" which can be repeated or just not present when compared to table 1 (so we know the file is not yet submitted).

I want to count how many times a file is submitted in Table 2 and bring that information into a new column in Table 1.

If I had an active relationship, this would be a simple measure = COUNTROWS(Table 2)
Placing the measure inside Table 1 automatically causes table 2 to filter for each "Register Code & Month-Yr" and count only those rows.

How do I replicate the same with an inactive relationship?

I've tried to use USERELATIONSHIP, but I don't know where I'm going wrong.

No of Files Submitted =

var _NoOfFiles = CALCULATE(COUNTROWS('Submitted Files'), USERELATIONSHIP('Required Files'[Register Code & Mth-Yr],'Submitted Files'[Register Code & Run Date]))

RETURN

IF(ISBLANK(_NoOfFiles),0, _NoOfFiles)

Thanks in advance for your help!

[Anonymous · Accepted solution]

Hi @DotU ,

Table 1 "Required Files"

Tabe 2 "Submitted Files"

Inacitve relationship

Now you could create a calculated column in "Required Files".

Column =
CALCULATE (
    COUNTROWS ( 'Submitted Files' ),
    FILTER (
        'Submitted Files',
        [Register Code & Run Date] = [Register Code & Mth-Yr]
    )
)

Best Regards,

Stephen Tao

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

[DotU]

Hi @Anonymous ,

What you provided works like a charm. Thank you ! For my work, I need to put additional filters on the Submitted Files table for:
File Purpose = "MPR"   and

File Type = "Threat"

This is how I modified the formula, it works, but I wanted to check with you if it is the best approach:

TIFilesSubmitted2 =

CALCULATE (

COUNTROWS('Submitted Files'),

FILTER(

'Submitted Files',

'Submitted Files'[Register Code & Run Date] = 'Required Files'[Register Code & Mth-Yr]),

FILTER('Submitted Files', 'Submitted Files'[File Type] = "Threat"),

FILTER('Submitted Files', 'Submitted Files'[File Purpose] = "MPR"))

A question I have with regards to this is, why do we have to use the FILTER function inside calculate - ie, shouldn't calculate be able to support filtering as the 2nd argument onwards by itself... I think its because of referring to another table, but I'm not sure how or why that matters cause we could have built the same as a measure instead of a column inside the Required Files table... now maybe im confusing myself too much, but any suggestions and insights would be appreciated!

On another note,
The following is what I was able to do before I received your response:

No of TI Files Submitted =

var _NoOf_TI_Files = CALCULATE(COUNTROWS('Submitted Files'),'Submitted Files'[File Type] = "Threat",'Submitted Files'[File Purpose] = "MPR", USERELATIONSHIP('Required Files'[Register Code & Mth-Yr],'Submitted Files'[Register Code & Run Date]))

RETURN

IF(ISBLANK(_NoOf_TI_Files),0, _NoOf_TI_Files)

Is my approach safe or would you suggest to avoid it?

Once again, many thanks!

[Anonymous]

Hi @DotU ,

About with and without the FILTER function in the CALCULATE function, you could refer to

powerbi - DAX Calculate function with and without FILTER - Stack Overflow

DAX Calculate function with and without FILTER

Which measure is better, this is difficult for me to answer, I can only say that both metrics are applicable. Because the performance of the measure also depends on many aspects.

You can go download DAX Studio to find out.

Best Regards,

Stephen Tao

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

[m3tr01d]

hi @DotU 

I see you have a dimension RequiredFiles and the column used for the relationship is Register Code & Mth.
Register Code & Mth : Is it some kind of concatenation of two fields?

What is the field used to define a RequiredFile?

[DotU]

Hi @m3tr01d ,

Thanks for the prompt response. Yes, I was concatenating two fields. And when I went into my file to send you some screen captures, I realized that in one table I concatenated using "-" while in the other I used " - ", and because of this any and all formuals I was trying were failing!

I'm surpised PowerBI even let me create a relationship if there were zero matches... hummm