Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dax Query

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 ...
Need Help
Tutorial Requests
mickey64's avatar
mickey64
Super User
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    mickey64
    Super User
    2 years ago

    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.