Dax Query

[Anonymous]

Hi Team,

I am new to Bi and stucked into one of the issue in which i want to show the minimum value in matrix. For example - suppose there are 3 trainings (A,B,C) and in india 3 people completed training A, 2 completed B, 1 person completed C respectively. So when i click the plus button in matrix to see the number of the people its showing me 3 but i want the minimum value i.e. 1. How can i get this. its important. 

Attaching screen shot for the reference. 

[mickey64]

Step 1: I use ’DATA’ table below.

Step 2: I make a measure.

    Min Number = CALCULATE(MIN('DATA'[Number]),ALLEXCEPT('DATA',DATA[Country]))

Step 3: Imake a matrix.

'DATA' table:

Country Trainings Number
India A 3
India B 2
India C 1
USA A 6
USA B 5
USA C 4
UK A 8
UK B 7
UK C 3

[Anonymous]

Hi Mickey64,

You have taken the min of number which is a numerical column for you but for me i have a cloumn as full name of the candidates who have completed the training and for that i can't take minimum.  Attaching screen shot of data as reference purpose.

So for name again i can't take minimum.

[mickey64]

Step 1: I use your data named 'DATA_2'.

Step 2: I make a summarize table named 'Country Table'.

    Country Table = SUMMARIZE('DATA_2','DATA_2'[Country],DATA_2[Trainings])

Step 3: I add 'Key' column  to the 'Country Table'.

    Key = [Country]&"_"&[Trainings]

Step 4: I add 'Key' column to the 'DATA_2' table.

    Key = [Country]&"_"&[Trainings]

Step 5: I add a relationship.

Step 6: I add 'Count' column to the 'Country Table'.

    Count = CALCULATE(COUNTROWS('DATA_2'),ALLEXCEPT('Country Table','Country Table'[Key]))

Step 7: I make 2 measures.

    Min Number_2 = CALCULATE(MIN('Country Table'[Count]),ALLEXCEPT('Country Table','Country Table'[Country]))

    Max Number_2 = CALCULATE(MAX('Country Table'[Count]),ALLEXCEPT('Country Table','Country Table'[Country]))

Step 8: I make a matrix.

[HarishKM]

@Anonymous Hello,
You can try this method as per image .

Thanks
Harish M

[Syndicate_Admin]