Need help with stacked bar chart visual

[sunit1190]

Hi,

I have the following measure that calculates number of users for a given date range

Count of users =

CALCULATE (

    DISTINCTCOUNT(MEETINGS_ADOPTION[USER_ID]),

    FILTER (

        MEETINGS_ADOPTION,

          (MEETINGS_ADOPTION[MEETING_DATE] <= MIN ( 'Calendar'[Date] )

            && MEETINGS_ADOPTION[MEETING_DATE] >= MAX ( 'Calendar'[Date] )) ||

            (MEETINGS_ADOPTION[MEETING_DATE] >= MIN ( 'Calendar'[Date] ) &&

             MEETINGS_ADOPTION[MEETING_DATE] <= MAX ( 'Calendar'[Date] ))

    )

)

My meetings adoption table has a status column which is calculated as 

Status =
SWITCH (
TRUE,
MEETINGS_ADOPTION[MEETING_ID] > 0 && MEETINGS_ADOPTION[MEETING_ID] <> MEETINGS_ADOPTION[MEETING_ID_W_R]
, "Has had a 1on1",
MEETINGS_ADOPTION[MEETING_ID_W_R] > 0 && MEETINGS_ADOPTION[MEETING_ID] = MEETINGS_ADOPTION[MEETING_ID_W_R], "Has had a 1on1 with rating",
"Has not had a 1on1"
)

On my stacked bar chart, when I plot the values as in the screenshot below I only see data for one status value - "Has had a 1on1" when filtered for the last 30 days. I want to be able to show the count of users who "Has not had a 1on1" and "Has had a 1on1" with rating" status values as well against each L2_Leader. How can I achieve that? 

[jdbuchanan71]

At this point I am going to need to see your data model and sample data in a file to be of any more help.

[sunit1190 · Accepted solution]

@jdbuchanan71 I was able to make this work by creating multiple measures and then plotting them on the bar chart. Thanks for all your help!

[jdbuchanan71]

Can you share your file so we can get the calculated column working?  In order to use it as a category in a chart, the field must be in a column in a table.

[sunit1190]

@jdbuchanan71 Unfortunately the calculated column approach won't work due to the way my dataset is structured. I was exploring if there is a way to do via a disconnected table but no luck so far. Do you have any insights around that? 

[jdbuchanan71]

No, you can't use a measure as an category on a chart.  You will have to get the calculated column working.

[sunit1190]

@jdbuchanan71 I was not quite able to get the calculated column working. Any thoughts if this can achieved in any other way?

[sunit1190]

@jdbuchanan71 okay got it! Thanks!

[jdbuchanan71]

@sunit1190 

If I understand correctly, you want the count of no 1-on-1 to include employees that are not in your meeting table, since they have not had a meeting.

I think you are going to need a higher level table to sit over your meeting table.   The higher level table would be and employee table and it would include their manager tagging.  That way you have something to count against : employees from the employee table that don't have a 1-on-1 meeting in the meeting table.

It is difficult to help with more complex modeling questions without a sample file to work on.

[sunit1190]

@jdbuchanan71 the users that have not had 1on1 are in my table but I realized that the calculated status column that i have created is throwing things off. 

I tried creating the below measure but it doesn't allow me to add this as a field under the 'legend' section in the bar graph 

Status_N =
SWITCH (
TRUE,
[Count of 1on1s] > 0
, "Has had a 1on1",
[Count of 1on1s with rating] > 0, "Has had a 1on1 with rating",
"Has not had a 1on1"
)

If there was a way to create this status as a measure that can be used on the graph that would solve it. I'm checking if I'm able to share the pbix and will get back to you. Thank you so much for your help! 

[sunit1190]

@jdbuchanan71 any thoughts on how I can create a measure that I can plot on the chart? 

[sunit1190]

@jdbuchanan71 Your measure solution got me close to what I want but I'm still missing one last step

In the screenshot below, I have filtered for the last 30 days and I accurately get count of 1on1s had by users under the highlighted leader on the bar chart as 18. However, the count of users who "Has not had 1on1" is 4 which is incorrect. For all the users under "Count of 1on1s" in the table that have value 0, I want to include them in this is bucket as well. 

Here is my updated DAX for the measure: 

Count of users =
CALCULATE (
DISTINCTCOUNT(MEETINGS_ADOPTION[USER_ID]),
(MEETINGS_ADOPTION[MEETING_DATE] >= MIN ( 'Calendar'[Date] ) &&
MEETINGS_ADOPTION[MEETING_DATE] <= MAX ( 'Calendar'[Date] )) ||
(MEETINGS_ADOPTION[MEETING_DATE] == BLANK() && MEETINGS_ADOPTION[MEETING_ID_W_R_DATE] == BLANK())
)

Any thourghts on how I can get to this? 

[sunit1190]

@jdbuchanan71 Thank you so much for your response! I really appreciate it! I will try again with the corrected measure that you have proposed and let you know if it works!

Yes there are rows that get tagged with "Has not had a 1on1" in the status column in my dataset. For these rows there is no meeting date assoiciated with it hence I added the below condition  to look for records that are outside of the date range provided in the filter. Also, my date filter is set to last 30 days by default. Do I need to add a condition that checks that says meeting_date is blank/null? Would that help? 

(MEETINGS_ADOPTION[MEETING_DATE] <= MIN ( 'Calendar'[Date] )

            && MEETINGS_ADOPTION[MEETING_DATE] >= MAX ( 'Calendar'[Date] ))  

Let me check if I'm allowed to share the pbix file and get back to you.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result in a simple Table format.

[sunit1190]

@Ashish_Mathur sure let me check if I can do that. Thank you for your response!

[jdbuchanan71]

@sunit1190 

First, a couple notes on your [Count of Users] measure.

You want to avoid applying a FILTER on an entire table.  It causes poor performance in the model and is not necessary.

Also, this line in your filter.
MEETINGS_ADOPTION[MEETING_DATE] <= MIN ( 'Calendar'[Date] ) &&

MEETINGS_ADOPTION[MEETING_DATE] >= MAX ( 'Calendar'[Date] )

Would only ever return a record if you had your Calendar table filtered to a single day.

Lets say you pick 6/1/2023 - 6/30/2023 on your Calendar table.  

A meeting with a [MEETING_DATE] of 6/15/2023 would not match either criteria of that filter line

So we end up with a corrected measure like this.

Count of users =
CALCULATE (
    DISTINCTCOUNT ( MEETINGS_ADOPTION[USER_ID] ),
    MEETINGS_ADOPTION[MEETING_DATE] >= MIN ( 'Calendar'[Date] ) &&
    MEETINGS_ADOPTION[MEETING_DATE] <= MAX ( 'Calendar'[Date] )
)

Now, in the Status calculated column on your MEETINGS_ADOPTION, do any of the rows get tagged with "Has not had a 1on1"?

It seems like your measure should be working if there are any rows in the MEETINGS_ADOPTION with "Has not had a 1on1". 

Could you maybe share your .pbix file (post it to drop box and share the link here)?