Filtering multiple arrays

[TatianaGuihur]

Hi,

I would like to display some matrix for each "Ville" I check.  How can I use the same matrix to filter different villes?

If I check one "Ville" and one "Nom Ecole"  is shows one matrix with the right values but What I need is show all the "Nom Ecoles" by "Ville" at the same time in different arrays.  Thanks 

[v-cazheng-msft · Accepted solution]

Hi @TatianaGuihur 

You can try these steps.

1 Add Index to the table in Power Query

2 Get max value for every Nom Ecole in every Ville

max_ = MAXX(FILTER('Table','Table'[Ville]=EARLIER('Table'[Ville])&&'Table'[Nom Ecole]=EARLIER('Table'[Nom Ecole])),'Table'[Index])

3 Rank by the max value

rank_ = RANKX(FILTER('Table','Table'[Ville]=EARLIER('Table'[Ville])),'Table'[max_],,ASC,Dense)

4 Create Measures for every matrices

Total for Max_1 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Ville]=SELECTEDVALUE('Table'[Ville])&&'Table'[rank_]=1))

Total for Max_2 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Ville]=SELECTEDVALUE('Table'[Ville])),'Table'[rank_]=2)

...

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TatianaGuihur]

I really appreciate the measure you created for me.  They worked excellent!!

[TatianaGuihur · Accepted solution]

Thank you so much @v-cazheng-msft  for your reply. I was able to do it.

[v-cazheng-msft · Accepted solution]

Hi @TatianaGuihur 

You can try these steps.

1 Add Index to the table in Power Query

2 Get max value for every Nom Ecole in every Ville

max_ = MAXX(FILTER('Table','Table'[Ville]=EARLIER('Table'[Ville])&&'Table'[Nom Ecole]=EARLIER('Table'[Nom Ecole])),'Table'[Index])

3 Rank by the max value

rank_ = RANKX(FILTER('Table','Table'[Ville]=EARLIER('Table'[Ville])),'Table'[max_],,ASC,Dense)

4 Create Measures for every matrices

Total for Max_1 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Ville]=SELECTEDVALUE('Table'[Ville])&&'Table'[rank_]=1))

Total for Max_2 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Ville]=SELECTEDVALUE('Table'[Ville])),'Table'[rank_]=2)

...

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.