Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kasebast
Frequent Visitor

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 date" follows the data of "created week". If i added "closed week" coloumn 1st the "created week" follows the data of "closed week". And hence you can see the that the chart is showing same values on y axis for both datas. However when i plot separetly i.e "count of created week" VS "created week" and "count of closed week" VS "closed week" the data is plotted correctly. Can someone please help me with a solution?

 

 

selection.PNGchart.PNGdataset.PNG

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.