Forum Discussion

mrleijzer's avatar
mrleijzer
Helper I
6 years ago

USERELATIONSHIP not working correctly

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? 

Need Help

17 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    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?

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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/

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Does this work?

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

  • mrleijzer's avatar
    mrleijzer
    Helper I
    6 years ago

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

    • JarroVGIT's avatar
      JarroVGIT
      Resident Rockstar
      6 years ago

      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's avatar
        mrleijzer
        Helper I
        6 years ago

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      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.

  • mrleijzer's avatar
    mrleijzer
    Helper I
    6 years ago

    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's avatar
      v-lid-msft
      Community Support
      6 years ago

      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's avatar
        mrleijzer
        Helper I
        6 years ago

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

    • mrleijzer's avatar
      mrleijzer
      Helper I
      3 years ago

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