Dynamic Change of data type by filtered value

[Harpreet1405_12]

Hello,

I want to create a pie chart that will display the data with different data types when selecting different parameters in same pie chart, i have pivoted column.

[v-xinruzhu-msft]

Hi @Harpreet1405_12 

You can create measure and use switch function.

e.g 

SWITCH(TRUE(),para1,Datatype1,para2,Datatype2,,,,,)

You can refer to tje following link to know more about switch() function

SWITCH function (DAX) - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

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

[Harpreet1405_12]

Thank you so much for your response, I want to switch to the datatype in a pivoted column, for example from the whole number datatype to the currency data type in the pie chart or other visualization. the switch function does not allow me to enter the datatype function. The Average hourly rate in the second column should be in fixed decimal with currency datatype whereas "Headcount" and "Number of Contracts Processed" should be in 

2017-2018	Headcount	# PT Contracts	161
2017-2018	Headcount	# Sessional Contracts	49
2017-2018	Headcount	# Partial Load Contracts	51
2018-2019	Headcount	# PT Contracts	168
2018-2019	Headcount	# Sessional Contracts	35
2018-2019	Headcount	# Partial Load Contracts	73
2019-2020	Headcount	# PT Contracts	176
2019-2020	Headcount	# Sessional Contracts	35
2019-2020	Headcount	# Partial Load Contracts	85
2020-2021	Headcount	# PT Contracts	193
2020-2021	Headcount	# Sessional Contracts	30
2020-2021	Headcount	# Partial Load Contracts	70
2021-2022	Headcount	# PT Contracts	199
2021-2022	Headcount	# Sessional Contracts	24
2021-2022	Headcount	# Partial Load Contracts	90
2017-2018	Number of contract processed	# PT Contracts	225
2017-2018	Number of contract processed	# Sessional Contracts	131
2017-2018	Number of contract processed	# Partial Load Contracts	196
2018-2019	Number of contract processed	# PT Contracts	243
2018-2019	Number of contract processed	# Sessional Contracts	91
2018-2019	Number of contract processed	# Partial Load Contracts	202
2019-2020	Number of contract processed	# PT Contracts	260
2019-2020	Number of contract processed	# Sessional Contracts	105
2019-2020	Number of contract processed	# Partial Load Contracts	227
2020-2021	Number of contract processed	# PT Contracts	296
2020-2021	Number of contract processed	# Sessional Contracts	104
2020-2021	Number of contract processed	# Partial Load Contracts	243
2021-2022	Number of contract processed	# PT Contracts	307
2021-2022	Number of contract processed	# Sessional Contracts	103
2021-2022	Number of contract processed	# Partial Load Contracts	268
2017-2018	Average hourly rate	# PT Contracts	60.25
2017-2018	Average hourly rate	# Sessional Contracts	49.47
2017-2018	Average hourly rate	# Partial Load Contracts	97.89
2018-2019	Average hourly rate	# PT Contracts	95.44
2018-2019	Average hourly rate	# Sessional Contracts	82.93
2018-2019	Average hourly rate	# Partial Load Contracts	82.93
2019-2020	Average hourly rate	# PT Contracts	99.5
2019-2020	Average hourly rate	# Sessional Contracts	90.41
2019-2020	Average hourly rate	# Partial Load Contracts	104.19
2020-2021	Average hourly rate	# PT Contracts	104.07
2020-2021	Average hourly rate	# Sessional Contracts	102.08
2020-2021	Average hourly rate	# Partial Load Contracts	108.05
2021-2022	Average hourly rate	# PT Contracts	106.95
2021-2022	Average hourly rate	# Sessional Contracts	97.05
2021-2022	Average hourly rate	# Partial Load Contracts	108.4

[v-xinruzhu-msft]

Hi @Harpreet1405_12 

You can use convert() function.

e.g 

Measure = SWITCH(TRUE(),MAX([Column2])="Average hourly rate",CONVERT(SUM([Column4]),CURRENCY),MAX([Column2])="Headcount",CONVERT(SUM([Column4]),STRING))

Output

You can refer to the following link to know more about the function.

CONVERT function (DAX) - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

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

[Harpreet1405_12]

Thank you once again for your valuable response, but this is not showing the values for headcount and the number of contracts processed for the pie chart or table. 

The output should be:-

[v-xinruzhu-msft]

Hi @Harpreet1405_12 

Can you provide the detail data type of  "Number of Contracts Processed" and "Headcount" the information you have offered only display the type of the "Average hourly rate" field.

Best Regards!

Yolo Zhu

[Harpreet1405_12]

The explanation of the data types is as follows:-

1. Headcount = whole number

2. Number of Contracts processed = Whole number

3. Average Hourly Rate = Currency (dollars)

All i want is that pie chart will display the data type based on the filter on column 2.

[v-xinruzhu-msft]

Hi @Harpreet1405_12 

After testing, the date type changing can be realized in table visual,but cannot be displayed in pie chart. maybe you can try create two mesaures , one measure type is whole number, the other type is currency, then create two pie charts and use bookmark to control when the pie to display, use the bookmark navigation button as slicer. you can refer to the following links.

https://learn.microsoft.com/en-us/power-bi/create-reports/button-navigators?tabs=powerbi-desktop#boo...

Create report bookmarks in Power BI to share insights and build stories - Power BI | Microsoft Learn

If you want to display it in table visual, you can refer to the following measure.

Measure = SWITCH(TRUE(),MAX([Column2])="Average hourly rate",FORMAT(SUM([Column4]),"$#.##"),OR(MAX([Column2])="Headcount",MAX([Column2])="Number of contract processed"),CONVERT(SUM([Column4]),INTEGER))

Best Regards!

Yolo Zhu

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