topic Re: Retrieve Top N list on a global period to display by month in DAX Commands and Tips

Retrieve Top N list on a global period to display by month

Anonymous — Mon, 13 Jan 2020 13:01:25 GMT

Hi everybody,

I have a problem, probably related to context, I hope you gonna help me.

I'm trying to display:

A table containing the list of Top N parameters on the total period: OK
A chart with these Top N parameters, with their values by month: KO. My problem is that I obtained Top N parameters for each month whereas I want the same parameters during the total period (regardless they were in the top N during the month)

Here are the tables:

[Source_Unpivot] * --- 1 [Source] linked with ID

[Source] 1 --- * [SourceDetails] linked with ID

[SourceDetails] 1 --- * [SourceDetailsDetails] linked with IDdetails

[Source_Unpivot] has 3 columns: ID, Parameter, Value.

On the dashboard, I let the users choose:

The period he wants to display (what if parameter "MyDisplayNmonths" + drop list with last month)
The parameter (drop list from the pivot table)
A what if parameter "MyTopN"

My measures:

LastNmonths_Count = CALCULATE( DISTINCTCOUNT(SourceDetailsDetails[IDdetails]); DATESINPERIOD(DATES_TABLE[Date];MAX(DATES_TABLE[Date]); - DisplayNmonths[MyDisplayNmonths];MONTH); CROSSFILTER(Source[ID]; Source_Unpivot[ID]; Both) ) ParameterRank_Count = RANKX(ALLSELECTED(Source_Unpivot[Value]);[LastNmonths_Count];;DESC) ParameterRank_Count_Visible = IF([ParameterRank_Count]<=[MyTopN]; [LastNmonths_Count])

In my table on the global period, I put Value and ParameterRank_Count_Visible: OK, I only see the top N parameters.

On the chart, I put:

X axis: MyDateFromSourceDetailsDetails
Y axis: ParameterRank_Count_Visible
Legend: Value

Problem: it shows me for instance the top 3 parameters in January, the top 3 parameters in February which might not be the same as in January, … whereas I want to see only the top 3 parameters displayed in my table (so the top 3 parameters on the global period), so I want the same 3 parameters in January, in February, … even if they are not in the top 3 during the month. How can I display only the top 3 parameters on the global period but displaying them by month ?

Hope this is clear.

Thanks in advance for your help !

Re: Retrieve Top N list on a global period to display by month

Stachu — Wed, 22 Jan 2020 08:10:26 GMT

in the chart column changes the filter context to single month, that's why you see month specific top values

in general you can workaround it using something like this (depending on your data structure):

CALCULATE([Measure], ALL(Table[Month])) CALCULATE([Measure], ALLSELECTED(Table[Month]))

where Table[Month] is the column you put in the X axis

If you add sample tables (in format that can be copied to PowerBI, see below) from your model with anonymised data, then I could be more specific regarding the syntax

Column1	Column2
A	1
B	2.5

Re: Retrieve Top N list on a global period to display by month

Anonymous — Wed, 22 Jan 2020 16:36:45 GMT

Hi everybody,

Stachu Thank you very much for your reply, I really appreciate your help. Indeed, it is a problem of context but I don't succeed to resolve it.

I put attached images: the relations between tables and some explanations about what I expect in the chart.

Here are some data samples, I hope I will use the good formatting in order for you to copy paste.

Table Source:

ID	Parameter1	Parameter2
A	X	abc
B	Y	def
C	Y	ghi
D	Y	def
E	Z	ghi
F	Z	def

Table Source_Unpivot:

ID	Parameter	Value
A	Parameter1	X
B	Parameter1	Y
C	Parameter1	Y
D	Parameter1	Y
E	Parameter1	Z
F	Parameter1	Z
A	Parameter2	abc
B	Parameter2	def
C	Parameter2	ghi
D	Parameter2	def
E	Parameter2	ghi
F	Parameter2	def

Table SourceDetails:

ID	IDdetails
A	A1
A	A2
A	A3
B	B1
B	B2
C	C1
D	D1
E	E1
F	F1

Table SourceDetailsDetails:

ID	IDdetails	SubID_Item	Date
A	A1	A1_1	05/02/2019
A	A2	A2_1	15/01/2019
A	A2	A2_2	15/03/2019
A	A2	A2_3	06/02/2019
A	A2	A2_4	06/02/2019
B	B1	B1_1	05/02/2019
B	B1	B1_2	07/01/2019
B	B1	B1_3	07/02/2019
C	C1	C1_1	12/01/2019
C	C1	C1_2	17/01/2019
D	D1	D1_1	07/02/2019
E	E1	E1_1	09/03/2019
F	F1	F1_1	09/03/2019

Table DATES_TABLE (in reality there are all the days for each month):

Date	YearMonth	YeaMonthNo
01/01/2019	JAN 2019	2019_01
02/01/2019	JAN 2019	2019_01
03/01/2019	JAN 2019	2019_01
01/02/2019	FEV 2019	2019_02
02/02/2019	FEV 2019	2019_02
03/02/2019	FEV 2019	2019_02
01/03/2019	MAR 2019	2019_03
02/03/2019	MAR 2019	2019_03
03/03/2019	MAR 2019	2019_03

Hope this will be more clear.

Thank you for your help !

Re: Retrieve Top N list on a global period to display by month

Anonymous — Fri, 31 Jan 2020 11:16:59 GMT

Hi everybody, Hi Stachu ,

I'm still stuck on my problem, I don't know how to resolve it.

I have tried the following formula in order to create a table that will contain only the rows matching with the parameter selected by the user. So I wanted to get only rows from Source_Unpivot where Parameter = 'the one selected by the user'. I was thinking to use the function SUMMARIZE but I'm not sure this is the good one as I don't understand how to use it: I created a column "test" but I don't need it, it is just to fill in the arguments...
And the following formula give me "insufficient memory" (whereas it is not even dynamic because Parameter1 is written instead of putting a measure that contain what the user has selected, and the final objective is to modify "<=3" by the what if parameter MyTopN.

TableTopN = VAR Source_Unpivot_Filtered = FILTER( SUMMARIZE(Source_Unpivot;Source_Unpivot[ID];"test";COUNTROWS(Source_Unpivot));[Parameter]='Parameter1' ) RETURN FILTER ( Source_Unpivot_Filtered; RANKX( CALCULATETABLE (Source_Unpivot ; ALLSELECTED(Source_Unpivot[Value])); [LastNmonths_Count ]; ; DESC ) <= 3 )

With LastNmonths_Count the same measure as before:

Thank you if you can help me with this.
Regards