cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Choosing a column from a table using slicer

Hi, experts!

I am making a Salary Analysis report (matrix), which shows number of employees in certain categories (work experience by qualification). This is the example:

 

Matrix.png

 

 

 

 

 

 

 

 

 

My database consists of 3 tables: Employees, Calculations and auxiliary table for making granulation of work experience.
Simplification of the tables:

1) Employees: (I have data for employment date and prior experience in columns Years_of_Service, Months_of_Service and Days_oF_Service as well as Qualification codes)

 

Employees.png

 

 

 

 

 

 

 

 

 

 

2) Calculations: (Number of Calculation and Calculation Period)

 

Calculations.png

 

 

 

 

 

 

 

 

3) Auxiliary table for work experience:

 

Work Experience.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The goal is to show number of employees in corresponding categories based on chosen calculation (in a slicer).

To simplify: for each chosen calculation I have to recalculate the experience of the employee based on the date of calculation (to make things a bit more complicated - we should use the last day of that month... this is not too big of a problem). AAAAAND... if no calculation is chosen in the slicer - make it for TODAY! (more precise, the day of refreshing the data).... AAAAAND one more tiny bit of info: if more than one calculation is chosen (because this is not the only report on the dashboard), than I should use the date of THE YOUNGEST calculation. AAAAAAND there is one more slicer on the same dashboard (choosing years, quartals, months, weeks...) which influences the selection of calculations.

 

My initial idea was to beforehand recalculate work experience for each employee and for each calculation (+ for "today") and provide that data in different columns (a column for each calculation), next to employee data. One additional column for each calculation, dynamicaly generated on refreshing of database.

 

And after 3 days of headache, reading all possible resources on the Internet, I came up with this:

Behold the table 'Calculated Experience'! 😉

 

Calculated Experience.png


 

 

 

 

 

I will post the solution (the "M" code alongside with the explanation) in the reply of this forum thread.

 

 

FINALLY, we come to the question!

 

How to make measure (or measures) in DAX which will choose: which column to use in the report/matrix, depending on the slicer choices (as explained in "the goal")?

 

One of the solutions includes unpivoting the table "Calculated Experience", and I am very close to it, but I need someone to revise my code and correct my bugs. This buggy code is also going to be published in the reply of this thread.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Choosing a column from a table using slicer

I think I know what the problem is!

 

The matrix (and also stacked bar chart or stacked column chart) persistantly shows me the values for TODAY, no matter which combination of slicers I make. This indicates that slicers have no effect on my measure...

 

The conditions (IFs) work fine, I have tested them... they react to slicer choices. Something is wrong with the functions I use (COUNTX, and I even tried with SUMX and then making a new measure as COUNTX('Calculated Experience'; [SelectedCalculation]) )

 

Any ideas?

View solution in original post

5 REPLIES 5
Highlighted
Frequent Visitor

Re: Choosing a column from a table using slicer

First of all, I would like to thank to all the great guys on the internet sharing knowledge. I have to mention all the resources I have used in order to solve the first part of my problem (to dynamically make as much columns as there are calculations... I don't know if there is suitable pattern for this kind of problem and I have no doubts that someone can think of better, more efficient solution):

 

MSDN

https://stackoverflow.com/questions/38251421/powerquery-adding-multiple-columns

https://blog.crossjoin.co.uk/2013/04/16/applying-a-function-to-every-cell-in-a-table-in-data-explore...

https://social.technet.microsoft.com/Forums/en-US/c3993594-461d-4e7e-81c4-06158db48d28/power-query-f...

https://social.technet.microsoft.com/Forums/en-US/5ded0f8d-f606-47e4-9ee1-7bbe6f72dd6b/passing-a-dyn...

https://datachant.com/2016/06/02/power-query-list-accumulate-unleashed/

http://excel-inside.pro/blog/2016/06/17/sumproduct-and-for-each-loops-in-power-query-implementations...

 

And this is my solution (of the first part of the problem) in Advanced Editor:

 

let
    Source = xxx...
    #"Renamed Columns" = Table.RenameColumns(Source,xxx...
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",xxx...
    #"Source 1 Final" = Table.AddColumn(#"Removed Columns", 
                                        "Today", 
                                         each Number.IntegerDivide(Duration.Days(Duration.From(DateTime.LocalNow()-[Employment_Date]))
                                                                  +365*[Years_of_Service]+30*[Months_of_Service]+[Days_of_Service], 365) ),

    Source2 = xxx...
    #"Changed Type2" = Table.TransformColumnTypes(Source2,xxx...
    #"Source 2 Final" = Table.RemoveColumns(#"Changed Type2",xxx...

    NumberOfColumns = Table.RowCount(#"Source 2 Final"),

    GetName = (x) =>
        let
            InputNames = #"Source 2 Final"{x}[No]
        in
    InputNames,    

    GetDate = (d) =>
        let
            InputDates = #"Source 2 Final"{d}[Calcul_Period],
            EndOfMonth = Date.EndOfMonth(InputDates)
        in
    EndOfMonth,

    ListOfNames = Table.ToColumns(#"Source 2 Final"){0},
    ListOfDates = Table.ToColumns(#"Source 2 Final"){1},

    AddColumnsFromList = (tableA, listX) =>
        let
            FinalTable = List.Accumulate(listX, [tableX=tableA, index=0], (state, current) => 
                [
                    tableX=Table.AddColumn(state[tableX], 
                                           GetName(state[index]), 
                                           each List.Max( { Number.IntegerDivide(Duration.Days(Duration.From(GetDate(state[index])
                                                                                 -DateTime.Date([Employment_Date])))
                                                                                 +365*[Years_of_Service]+30*[Months_of_Service]+[Days_of_Service], 365),
                                                            0}, 0) 
                                           ),
                    index=state[index]+1 
                ]
            )[tableX]
        in
    FinalTable,

    #"Final Table" = AddColumnsFromList(#"Source 1 Final", ListOfNames)
in
    #"Final Table"

 

 

The key is in the function AddColumnsFromList(), which iterates through the list of names of calculations, and for each calculation makes additional column (with corresponding name).

Max() function is there to prevent negative numbers of showing in the final table (if calculation date is prior to employment date).

Highlighted
Frequent Visitor

Re: Choosing a column from a table using slicer

 

The second part of the problem is to show the appropriate column in the matrix as the user chooses different values in slicers (one of the slicers is calculation choice with ability to select multiple calculations, and the other slicer chooses years, quartals, months, weeks...)

 

One of the suggestions I considered for this is to UNPIVOT all the dynamically created columns and then filter by chosen value in the new column (with calculation numbers). The code in Advanced Editor that appends on previous is:

 

 

    #"Final Table" = AddColumnsFromList(#"Source 1 Final", ListOfNames),
    // --- start append ----- (don't forget the comma on the end of the previous line)
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Final Table", {"Employee_No", "Years_of_Service", "Months_of_Service", "Days_of_Service", "Employment_Date", "Qualification"}, "Calculation No", "Total Years of Service"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Total Years of Service", Int64.Type}})
in
    #"Changed Type"

 

The trick is to select all the columns I wish to keep and then unpivot ALL THE OTHERS (that way, I unpivot all of them, as many of them there are => as many calculations there are).

 

Now I have to make a measure to use on my matrix, which will satisfy all the conditions mentioned in the goal of my first post: 

 

 

SelectedCalculation = IF( ISCROSSFILTERED('Calculations'[No]);
// If some calculations are selected by slicer selections
IF( HASONEVALUE('Calculations'[No]);
// If only one calculation is selected, put that calculation number in text (with function CONCATENATEX),
// filter the table on that criterion, and finally - count the rows
COUNTX(FILTER('Calculated Experience';
'Calculated Experience'[Calculation No] = CONCATENATEX(VALUES('Calculations'[No]);
'Calculations'[No];
", ")
);
'Calculated Experience'[Total Years of Service]
);
// ELSE, if multiple calculations are selected, take the last of them, put it in a text (with func. CONCATENATEX),
// filter the table on that criterion, and count the rows
COUNTX(FILTER('Calculated Experience';
'Calculated Experience'[Calculation No] = CONCATENATEX(LASTNONBLANK(VALUES('Calculations'[No]);
true);
'Calculations'[No];
", ");
);
'Calculated Experience'[Total Years of Service]
)
);
// ELSE (if no calculations were selected by slicers - take "Today")
COUNTX(FILTER('Calculated Experience';
[Calculation No] = "Today"
);
[Total Years of Service]
)
)

 

 AAAAAAND, there is a bug somewhere. How do I know? Well, I made a test:

The first worker (E00001) was employed in 2006 and should have 10 years of experience on EACH calculation, but 11 years on TODAY.

 

I made one calculated column for reference

ReferenceColumn = MAX('Calculated Experience'[Total Years of Service]; 0) )

and put it in a matrix:

 

Control Matrix.png

 

 

 

 

 

 

When I use my measure in a matrix, for Qualification "II" I always get 22 employees in category "6 - 10 years" and 1 employee in "11 - 15 years", and that should be the case ONLY when no selection with slicers has been made. With each selection of slicers, there should be 23 employees in category "6-10 years". I am not able to find this bug, and whether the problem lies in my DAX code or in the PowerBI internal Matrix visualisation.

 

This solution (with UNPIVOTING) is very inefficient, because it makes HUGE tables (1000 employees TIMES 30 calculations - already makes considerable amount of records). I would prefer a solution which deals with the table prior to unpivoting, if possible (I have a feeling that this is not possible with DAX)

Highlighted
Frequent Visitor

Re: Choosing a column from a table using slicer

I think I know what the problem is!

 

The matrix (and also stacked bar chart or stacked column chart) persistantly shows me the values for TODAY, no matter which combination of slicers I make. This indicates that slicers have no effect on my measure...

 

The conditions (IFs) work fine, I have tested them... they react to slicer choices. Something is wrong with the functions I use (COUNTX, and I even tried with SUMX and then making a new measure as COUNTX('Calculated Experience'; [SelectedCalculation]) )

 

Any ideas?

View solution in original post

Highlighted
Frequent Visitor

Re: Choosing a column from a table using slicer

 

There is one good advice, found on site http://www.daxformatter.com/ in comments:

 

Calculate 1.png

 

 

 

 

 

 

 

 

 

 

Calculate 2.png

 

 

 

 

 

 

 

 

 

 

I will refactor my code now and see if it helps! 🙂

Highlighted
Frequent Visitor

Re: Choosing a column from a table using slicer

 

Unfortunately, refactoring the code didn't solve the issue. I still get the results for "Today" and they don't change when I change various slicers... At least, I know that my code is more efficient now... 😉

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors