Forum Discussion

kasebast's avatar
kasebast
Frequent Visitor
7 years ago
Solved

Line and clustered column chart

Hi, I tried adding datas to this chart. However my 2 datas that i want as coloumns(which are different) seem to follow the other. Example if i added "created week" to coloumn values 1st, "closed dat...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    7 years ago

    Hi kasebast ,

     

    You need to create a bridge table with all the weeks on your created and closed week, then make two relationships between that table and the two columns on your data table.

     

    Then create the following measures:

    Created = COUNT(Table1[Created Week])

Closed =
IF (
    MAX ( WEEK[WEEK] ) = BLANK ();
    BLANK ();
    CALCULATE (
        COUNT ( Table1[Closed Week] );
        USERELATIONSHIP ( Table1[Closed Week]; WEEK[WEEK] )
    )
)

    Assuming that the active relationship is on the open date.

     

    Then add the week from the bridge table on x-axis and the measures on values.

     

    Check PBIX file attach.

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
7 years ago

Hi kasebast ,

 

You need to create a bridge table with all the weeks on your created and closed week, then make two relationships between that table and the two columns on your data table.

 

Then create the following measures:

Created = COUNT(Table1[Created Week])

Closed =
IF (
    MAX ( WEEK[WEEK] ) = BLANK ();
    BLANK ();
    CALCULATE (
        COUNT ( Table1[Closed Week] );
        USERELATIONSHIP ( Table1[Closed Week]; WEEK[WEEK] )
    )
)

Assuming that the active relationship is on the open date.

 

Then add the week from the bridge table on x-axis and the measures on values.

 

Check PBIX file attach.

Regards,

MFelix

kasebast's avatar
kasebast
Frequent Visitor
7 years ago

Hi MFelix,

 

Thank you so much for helping me out. I had some issues with creating a bridge and measures porbably cuz i hadnt done any yet but now the issue is solved. Thanks a lot. But I would really like to know how does the USERELATIONSHIP command filter? I tried understanding but i still dont have a clear understanding. It would be great if you could explain it a bit. 

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi kasebast ,

     

    The USERELATIONSHIP formula specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2, in this case what is doing is making active and inactive relationship between two tables making the calculation being forced to use the columns referene in the syntax.

     

    Regards,

    MFelix