Forum Discussion

simrantuli's avatar
simrantuli
Continued Contributor
6 years ago
Solved

Issues in Matrix visual

Hi All,   I am facing an issue while trying to show some information in 'Matrix'.  So, I have below two tables. 'Table' table: 'Mapping' table: What I want to show in 'Matrix'...
Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    simrantuli - I did it like the following:

     

    Measure = 
    VAR __Key = MAX('Table'[#Key_Site_Supplier])
    VAR __Site = MAX('Table (2)'[Site])
RETURN
    MAXX(FILTER('Table (2)',[#Key_Site_Supplier] = __Key && [Site] = __Site),[Supplier])

     

    PBIX is attached below sig.

     

    Also, you could achieve this just using "First Supplier" if you make your relationship filter direction "Both". Then you don't need the DAX. But, if for some reason you can't do that in your model, the DAX should work.

simrantuli's avatar
simrantuli
Continued Contributor
6 years ago

Hi Fowmy,

 

I am not expecting multiple values in Matrix as if you see the data also, there are no multiple suppliers for a single site and service combination.

Please find below the data.

 

'Table' table:

 

Service   #Key_Site_Supplier

FoodA1##A
AVA2##B
FitnessA3##C
FoodB1##D
FitnessB2##A
FoodB3##A
FitnessB3##A
AVB3##B
AVB4##A
FoodB4##B

 

'Mapping' table:

 

Site Supplier #Key_Site_Supplier

A1AA1##A
A2BA2##B
A3CA3##C
B1DB1##D
B2AB2##A
B3AB3##A
B3BB3##B
B4AB4##A
B4BB4##B

 

Best Regards

Simran Tuli

Fowmy's avatar
Fowmy
Super User
6 years ago

simrantuli 

Do you have a single table or two tables with a relationship? 

You can share both if so

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply šŸ™‚

YouTube, LinkedIn

  • simrantuli's avatar
    simrantuli
    Continued Contributor
    6 years ago

    Hi Fowmy,

     

    I have two tables with a relationship.

    'Table' table:

     

    Service   #Key_Site_Supplier

    FoodA1##A
    AVA2##B
    FitnessA3##C
    FoodB1##D
    FitnessB2##A
    FoodB3##A
    FitnessB3##A
    AVB3##B
    AVB4##A
    FoodB4##B

     

    'Mapping' table:

     

    Site Supplier #Key_Site_Supplier

    A1AA1##A
    A2BA2##B
    A3CA3##C
    B1DB1##D
    B2AB2##A
    B3AB3##A
    B3BB3##B
    B4AB4##A
    B4BB4##B

     

    I want to show 'Site' as Row, Service as 'Column' and 'Suppliers' as Values in Matrix.

     

    Thanks!

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      simrantuli - I did it like the following:

       

      Measure = 
    VAR __Key = MAX('Table'[#Key_Site_Supplier])
    VAR __Site = MAX('Table (2)'[Site])
RETURN
    MAXX(FILTER('Table (2)',[#Key_Site_Supplier] = __Key && [Site] = __Site),[Supplier])

       

      PBIX is attached below sig.

       

      Also, you could achieve this just using "First Supplier" if you make your relationship filter direction "Both". Then you don't need the DAX. But, if for some reason you can't do that in your model, the DAX should work.

      • simrantuli's avatar
        simrantuli
        Continued Contributor
        6 years ago

        Greg_Deckler You are AWESOME!

        It worked. Thank you so much!

         

        Just one question. Why doesn't the below measure work in this case? What's the issue? If you could explain. 

        SELECTEDVALUE(Mapping[Supplier])

         

        Thanks!