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 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. 

 

 

Need Help
Tutorial Requests

5 Replies

  • HarishKM's avatar
    HarishKM
    Super User
    2 years ago

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



    Thanks
    Harish M

     

  • 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.