USERELATIONSHIP not working correctly

[mrleijzer]

Hello all,

I have two tables, Calendar and Projecttakken.

There's a main active relationship between Calendar[Date] and Projecttakken[Boekdatum].

There's also an inactive relationship between Calendar[Date] and Projecttakken[Gewijzigd].

For the main part, my visuals are great and work fine.

Except there's one viz in which I need to see all documents from [Gewijzigd] that have status "Vervallen", in the year the slicer shows.

I've used USERELATIONSHIP before with no trouble, so I made this measure:

Measure = CALCULATE(COUNT(Projecttakken[Boeknummer]);Statussen[Statuscategorie]="Vervallen";USERELATIONSHIP('Calendar'[Date];Projecttakken[Gewijzigd]))

As I check I expect to see 39 records, but the measure outcome is (Blank).

What am I doing wrong? 

[luamar]

@mrleijzer did you find any solution to this? I am having exactly the same issue

[mrleijzer]

Unfortunately I don't remember how I solved this eventually...

[mrleijzer]

Back to the drawing board...

Measure= CALCULATE(DISTINCTCOUNT(Projecttakken[Boeknummer])

works fine, 1073 records, just like I expected.

Measure= CALCULATE(DISTINCTCOUNT(Projecttakken[Boeknummer]);Statussen[Statuscategorie]="Vervallen")

works fine also, getting and expecting 3 records

So when I add USERELATIONSHIP

Measure=

CALCULATE(DISTINCTCOUNT(Projecttakken[Boeknummer]);Statussen[Statuscategorie]="Vervallen";USERELATIONSHIP('Calendar'[Date];Projecttakken[Gewijzigd]))

It's blank again, so there must be a relationship problem, but where?

[v-lid-msft]

Hi @mrleijzer ,

We can try to use the following measure to meet your requirement:

Measure =
CALCULATE (
    COUNT ( Projecttakken[Boeknummer] );
    FILTER (
        ALLSELECTED ( Projecttakken );
        Projecttakken[Gewijzigd]
            IN FILTERS ( 'Calendar'[Date] )
            && RELATED ( Statussen[Statuscategorie] ) = "Vervallen"
    )
)

If it does not work, could you please describe the relationship between Projecttakken and Statussen tables simply if it does not contain any confidential information?

Best regards,

[mrleijzer]

Thanks!

Using your formula PowerBI says that the column Statussen[Statuscategorie] either doesn't exit or doesn't have a relationship in de current context.

Below an image of the two tables and how they're related (both decimal number format).

[v-lid-msft]

Hi @mrleijzer ,

Thank you for your additional information, could you please try to use the following measure?

Measure =
CALCULATE (
    COUNT ( Projecttakken[Boeknummer] );
    FILTER (
        ALLSELECTED ( Projecttakken );
        Projecttakken[Gewijzigd] IN FILTERS ( 'Calendar'[Date] )
    );
    Statussen[Statuscategorie] = "Vervallen"
)

Best regards,

[mrleijzer]

Thanks Dong Li, but it's still coming up blank.

[v-lid-msft]

Hi @mrleijzer ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[mrleijzer]

Sorry for not replying earlier.

I don't use filters, everything is filtered in measures.

I tried your calculations nevertheless but this also didn't work. Thanks for the suggestion.

[v-lid-msft]

Hi @mrleijzer ,

Where are you applied the 'Date'[Date] column, in the table visual or in the filter? We can try to use the following measur if you use it in the filter:

Measure =
CALCULATE (
    COUNT ( Projecttakken[Boeknummer] );
    FILTER (
        CALCULATETABLE (
            ALLSELECTED ( Projecttakken );
            'Calendar'[Date]
        );
        Projecttakken[Gewijzigd]
            IN FILTERS ( 'Calendar'[Date] )
    );
    Statussen[Statuscategorie] = "Vervallen"
)

Measure =
CALCULATE (
    COUNT ( Projecttakken[Boeknummer] );
    FILTER (
        ALL ( Projecttakken );
        Projecttakken[Gewijzigd]
            IN FILTERS ( 'Calendar'[Date] )
    );
    Statussen[Statuscategorie] = "Vervallen"
)

Best regards,

[mrleijzer]

Hey thanks for the quick replies!

Unfortunately they didn't solve the problem.

It's so weird, I've checked all the filters (there's really only one active on the Calendar[Date] field) and it keeps on coming out Blank.

I also tried running the operations in different orders, I've tried the FILTER function but it's no use.

Triple checked the relationships and it all looks good.

I'll keep trying, meanwhile, if you have any brilliant ideas, let me know ;)!

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Tell me the exact visual/card where you want to see 39 but are unable to see.

[JarroVGIT]

Is it possible to share your PBIX? (only if there is no confidential data in it). If preffered, you could PM me a link if you don't want to share publicly.

[mrleijzer]

Unfortunately, RM&C won't allow me to share this report / data.

[Ashish_Mathur]

Hi,

Does this work?

Measure = CALCULATE(COUNT(Projecttakken[Boeknummer]);Statussen[Statuscategorie]="Vervallen";USERELATIONSHIP(Projecttakken[Gewijzigd];'Calendar'[Date]))

[amitchandak]

You measure seems fine. Try following

1) Check any other filter on-page of drilled

2) Create the below measure

Measure1 = CALCULATE(COUNT(Projecttakken[Boeknummer]);USERELATIONSHIP('Calendar'[Date];Projecttakken[Gewijzigd]))

and now a table of matrix drag Statussen[Statuscategorie] and Measure1 

Are you able to see the value for Vervallen. If not you can found the issue/

[JarroVGIT]

Hi @mrleijzer ,

Your measure should work just fine. Only thing I can think of is that you are not actually filtering on the date calendar but on you have a filter set through the Boekdatum column?