Need help with a DAX function

[Vijay08V]

Hi,

I need some help with the DAX function for below logic

Problem statement : Need to bring a stacked bar chart visual where it shows total count of each category based on the latest Id number available for each dimension. Let me explain it with a sample data.

From below dataset, for the dimension - Customer there are totally 5 distinct elements. Now If I take the first element which is collaboration then as per the last ID which is Id 77 the category would be "Innovator". Moving on to the next element Confidence there is only one category transformer.Similary for the element Engagement as per latest Id 77 the category would be Innovator

Now considering only the first three elements of customer dimension . Total count of elements would be as below

Innovator - 2

Transformer -1

Expected output in the visual is as below

Sample Data :

Element	Category	Id	Dimensions
Collaboration	Innovator	77	Customer
Collaboration	Transformer	74	Customer
Confidence	Transformer	74	Customer
Engagement	Innovator	77	Customer
Engagement	Transformer	74	Customer
Experience	Transformer	74	Customer
GoToMarket	Innovator	77	Customer
GoToMarket	Transformer	74	Customer

[Anonymous · Accepted solution]

Hi @Vijay08V ,

I suggest you to add an index column by Element group in Power Query Editor. This will make the calculation easier, we need a number column or date column to confirm the order of priority.

For reference: Create Row Number for Each Group in Power BI using Power Query

New Table:

Measure:

Count = 
CALCULATE(DISTINCTCOUNT('Table'[Element]),'Table'[Index] = 1)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Vijay08V , You can use a measure

Distinctcount(Table[Element])

[Vijay08V]

@amitchandak Hi Amit - I tried using distinct count measure but its giving me total count of 8. However it should be 3 count for Inovator and 2 for Transformer element based on the latest Id number