Forum Discussion
Count as per Area
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.
Thanks I resolved it by myself with help of DAX using KEEEPFILTERS
7 Replies
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.
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 _Returnits showing all counts of IDS but not distinct counts for area
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.
Hi,
Share the download link of the PBI file with your measures already written.
Thanks I resolved it by myself with help of DAX using KEEEPFILTERS
