Count as per Area

[sarthsla]

Hi All,

I have below data in Table Visual in PBI desktop, where StatusMeasure is generated dynamically based on ID and few conditions

No when I want to count the Distinct IDs for each Area and Statusmeasure, I try to take off Login from table visual and everyrthing goes blank.

Any idea how to write a DAX for Distinctcount(ID)(on Y-axis) for each Area(on X-axis) with Status measure as legend 

So above should show as below

Please note StatusMeasure is a dynamically generated DAX based on row level values of Login in table visual and a date.

[sarthsla · Accepted solution]

Thanks I resolved it by myself with help of DAX using KEEEPFILTERS

[Ashish_Mathur]

Hi,

Share the download link of the PBI file with your measures already written.

[cengizhanarslan]

1) A measure can’t be used as a Legend category

In a column/stacked chart, Legend must be a column (categorical field). A DAX measure (your StatusMeasure) can’t create legend buckets by itself. So if you want “Yes/No” as the legend, you need a real column in the model (calculated column / in the source)

2) Your StatusMeasure depends on “Login being in the visual”

That usually happens when the measure uses something like SELECTEDVALUE(Widget[Login]).

When you remove Login from the visual, there are multiple logins in scope, thus, SELECTEDVALUE() becomes blank.So you need to rewrite the logic so it works at the grain you want (Area/ID), not at the grain of the table visual.

[GeraldGEmerick]

@sarthsla To do this, you generally need to use a table with no relationships that has a row for every potential value of your measure. You use that in your legend and then write DAX similar to the following for the distinct count:

StatusMeasure Count =
VAR _Legend = MAX( 'Legend Values'[Value] ) // this is your table with no relationships Yes/No
VAR _Table = DISTINCT( SELECTCOLUMNS( FILTER( ADDCOLUMNS( 'Table', "Status Measure", [StatusMeasure] ), [Status Measure] = _Legend ), "ID", [ID] ) )
VAR _Return = COUNTROWS( _Table )
RETURN _Return

[sarthsla]

its showing all counts of IDS but not distinct counts for area

[v-tejrama]

Hi @sarthsla ,

Thank you @cengizhanarslan  for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you.

[v-tejrama]

Hi @sarthsla ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.