Dax for calculating count of Login on Bar Chart

[srlabhe]

Hi PBI Gurus,

I have below table which is sample data of what I actually have

ID	Area	login	DynamicMeasure
1	East	111ABC	Assigned
1	East	123PQR	Unassigned
2	East	111ABC	Assigned
2	West	111ABC	Assigned
3	West	111ABC	Assigned
4	West	123PQR	Unassigned
5	West	456ABC	Unassigned

In Above table the first column ID is driver of data. If we take off first column the DynamicMeasure shows blank.

Also we cant use the measure in Graph's Legend. We have slicer on Area, DynamicMeasure (I build slicer on measure creating separate data table and using a flag to compare data table values and DynamicMeasure to select)

Now, when I am trying to put it on Graph it fails ,I need to put a distinct count of logins for each Area where DynamicMeasure in legend in clustred bar chart.

Any suggestion ofr DAX creation please ?

[v-dineshya · Accepted solution]

Hi @srlabhe ,

Thank you for reaching out to the Microsoft Community Forum.

As you mentioned in your query, you can't able to use measure in Graph's Legend. And you need to put a distinct count of logins for each Area where DynamicMeasurein legend in clustred bar chart.

You can't use measures in clustred bar chart legend, Instead of measure create calculated column. I have created calculated column "DynamicStatus" and placed in legend field.  And i have created "Login Distinct Count" measure to show "distinct count of logins for each Area"  in clustred bar chart. Please refer below output snaps and attached pbix file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @srlabhe ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @srlabhe ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[GeraldGEmerick]

@srlabhe You typically create a table without any relationships that has data corresponding to the possible measure values. So in your case, you would have a single column table with 2 rows for Assigned and Unassigned. You can then use those in the legend of your visuals. You can then create a measure similar to the following:

Distinct Count = 
VAR _Value = SELECTEDVALUE( 'Measure Value Table'[Value] ) //This is your non-connected table
VAR _Table = ADDCOLUMNS( 'Table', "MeasureValue", [DynamicMeasure] )
VAR _Return = COUNTROWS( DISTINCT( SELECTCOLUMNS( FILTER( _Table, [MeasureValue] = _Value ), "ID", [ID] ) ) )
RETURN _Return
      

[srlabhe]

This is not working as expected as Even Area is text based DAX measure here , so need to have the row context maintained even I take ID column off the Login column values should not change