DAX functionality

[darkstar00187]

Hi,

Who can help me to find a solution for my problem.

I've a table with the following content:

Account Name	Product Name	PRIO
Test	Test_1	1
Test	Test_2	2
Test	Test_4	4
List	Test_2	2
SOFTWARE	Test_15	5
SOFTWARE	Test_16	4

In a another table or if possible directly in 2 graphs I would like to have following output:

• Count the unique accounts based on the highest prio for each account
• Count the amount of product refered to the highest prio

Solution -->

PRIO/	1	2	3	4	5
Accounts	0	1	0	1	1
Producten	0	1	0	3	2

Thanks a lot!

Stephanie

[Greg_Deckler · Accepted solution]

OK, here we go. My base data table is Table2. Create a disconnected table with a [Name] column with two rows, "Accounts" and "Products". This is Table3 in my example. Now create the following measure:

Measure = 
VAR __maxPrio = MAX([PRIO])
VAR __tableAccounts = SUMMARIZE(ALL('Table2'),[Account Name],"__prio",MAX([PRIO]),"__products",COUNT([Product Name]))
VAR __countAccounts = COUNTX(FILTER(__tableAccounts,[__prio]=__maxPrio),[Account Name])
VAR __countProducts = SUMX(FILTER(__tableAccounts,[__prio]=__maxPrio),[__products])
RETURN
SWITCH(
    TRUE(),
    MAX('Table3'[Name])="Accounts",__countAccounts,
    MAX('Table3'[Name])="Products",__countProducts
)

Now create a matrix visualization with 'Table3'[Name] as the Rows, 'Table2'[PRIO] as the Columns and [Measure] as the Values.