pivot using dax

[Anonymous]

Hello,

I need help with Rank and Pivot data using filters. I've attached the sample data and the result.Data (Column H,I are derived using the criteria in Col L-P))Result in Table format

Columns - A to E has actual data; H and I are derived columns based on the criteria

I was able to achieve the results using

1. Switch case in Dax to derive "Status" column

2. Using Calculated table to group and get the select order based on Min value

3. Display the data using Matrix

Issue here is with matrix, i am unable to add custom tool tip to rows and not sort based on the column data. If i can present the same via a table. I will be able to use custom tool tips and also sort the columns

I tried to use another calculated table with summarize to transform the data but it is taking a while with 500k+ rows

Thank you in advance for your help

[v-lionel-msft]

Hi @Anonymous ,

Can you make a copy of your sample data in tabular form? Your example data is too much to build.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Greg, Thank you for sharing the article

@v-lionel-msft - Below is sample data. "status" and "select per order" fields are derived based on the criteria shared above

County	Client ID	Date	Group	Hrs			Status	Select per order
AB	1	3/22/2019	At Store	2			Visit
AB	1	3/22/2019	Online	6			O FT	O FT
AB	10	3/22/2019	Online	8			O FT	O FT
AB	11	3/22/2019	At Store	8			FT	FT
AB	13	3/22/2019	Online	4			O PT
AB	13	3/22/2019	At Store	5			FT	FT
AB	14	3/22/2019	At Store	12			Other	Other
CD	2	3/22/2019	Online	4			O PT	O PT
CD	3	3/22/2019	At Store	4			Visit	Visit
CD	4	3/22/2019	At Store	6			FT
CD	4	3/22/2019	Online	6			O FT	OFT
IJ	5	3/22/2019	At Store	11			FT	FT
KL	1	3/22/2019	Online	8			O FT	O FT
AB	1	3/23/2019	Online	6			O FT	O FT
AB	10	3/23/2019	At Store	3			Visit	Visit
AB	11	3/23/2019	At Store	4			FT	FT
AB	11	3/23/2019	Online	5			O PT
AB	13	3/23/2019	Online	6			O FT	O FT
AB	14	3/23/2019	Online	7			O FT	O FT
AB	14	3/23/2019	At Store	3			visit
AB	16	3/23/2019	At Store	9			FT	FT
CD	2	3/23/2019	Online	2			O PT	O PT
IJ	30	3/23/2019	At Store	8			FT	FT
IJ	31	3/23/2019	Online	6			O FT	O FT
IJ	31	3/23/2019	At Store	4			FT
IJ	32	3/23/2019	Online	5			O PT	O PT
IJ	33	3/23/2019	At Store	11			FT	FT

[Greg_Deckler]

Well, you would have to create a measure for each of the columns in your desired output. Other than that, tough to say. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490