topic Re: Create a visualization in tabular form in DAX Commands and Tips

Create a visualization in tabular form

SK87 — Mon, 08 Aug 2022 05:07:06 GMT

Hi All,

Here is one problem which I am facing while creating a tabular graph - I have values in different columns and need to show minimum & Maximum values in one table of respective columns and columns should be presented as rows.

Here is the sample dataset:

Date	A	B
2022-02-12	60	9'
2022-04-18	42
2022-03-25	42
2022-01-18	56	8'
2022-01-22	55
2022-06-22		16'
2022-07-09		9' 3"
2022-09-25		9' 3"
2022-10-02		9' 3"
2022-02-26	204
2022-05-04		9'
2022-05-09		9'
2022-06-14
2022-06-01	60	9'
2022-06-26	60	9'
2022-11-13	60	9'
2022-03-06
2022-11-01		11'
2022-12-17		11'
2022-07-06		12'
2022-02-13
2022-11-13		11'
2022-06-03		14'

Now I want below output:

Based on Date	Min	Max
A	40	204
B	8'	16'

Also want the units 'inches' in 'A' column values.

I had calculated the measures seperately for Min and Max but when I create table then getting o/p different

Please suggest.

Re: Create a visualization in tabular form

SK87 — Mon, 08 Aug 2022 09:06:22 GMT

Any solutions for above problem? Kindly help me anyone on this

amitchandak

tamerj1
Jihwan_Kim
SpartaBI

johnt75

Thanks in advance

Re: Create a visualization in tabular form

SpartaBI — Mon, 08 Aug 2022 09:17:06 GMT

SK87 are these values 9' 3'' text in your data?
I understand this is 9 feet and 3 inches but is it some formatting you did on numerical values or it's just text?

In general if these are numbers you could just need to unpivot the A and B columns and create a MAX and MIN measure on the new value column.

Showcase Report – Contoso By SpartaBI

Re: Create a visualization in tabular form

tamerj1 — Mon, 08 Aug 2022 10:25:39 GMT

Hi SK87
First you need to unpivot A and B columns using power query. Then a new column shall be created that converts all values to "Inch". The Min and Max measures shall be based on this column. Here is a sample file for your reference https://www.dropbox.com/t/RlIw4N0W7oLYqJRt

Value (Inch) = VAR String = Data[Value] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Value", VAR SingleItem = PATHITEM ( Items, [Value] ) VAR ItemLength = IF ( CONTAINSSTRING ( SingleItem, "'" ) || CONTAINSSTRING ( SingleItem, """" ), LEN ( SingleItem ) - 1, LEN ( SingleItem ) ) VAR Number = LEFT ( SingleItem, ItemLength ) RETURN IF ( CONTAINSSTRING ( SingleItem, "'" ), 12 * VALUE ( Number ), VALUE ( Number ) ) ) RETURN SUMX ( T2, [@Value] )

Min = VAR MinValue = MIN ( Data[Value (Inch)] ) VAR Foot = QUOTIENT ( MinValue, 12 ) VAR Inch = MOD ( MinValue, 12 ) RETURN IF ( Inch > 0, Foot & "' " & Inch & """", Foot & "'" )

Max = VAR MinValue = MAX ( Data[Value (Inch)] ) VAR Foot = QUOTIENT ( MinValue, 12 ) VAR Inch = MOD ( MinValue, 12 ) RETURN IF ( Inch > 0, Foot & "' " & Inch & """", Foot & "'" )

Re: Create a visualization in tabular form

SK87 — Mon, 08 Aug 2022 11:07:00 GMT

SpartaBI This is text value

tamerj1 While converting the values to Inches I am getting below error as Column B value is Text

Re: Create a visualization in tabular form

tamerj1 — Mon, 08 Aug 2022 11:36:22 GMT

SK87
Can you please share a screenshot of the source data? I just need to see how the values look like and what is the difference between your data and the sample data that I used in my sample file.

Re: Create a visualization in tabular form

SK87 — Mon, 08 Aug 2022 11:50:31 GMT

Here is the sample data and I am not able to open the data shared by you.

A	B	C	D	E
60	168	9'
42	143.5
42	143.5
56	167.7	8'
55	172			10000
		16'
	145	9' 3"
	145	9' 3"
	145	9' 3"
204	270			26000

After unpivoting I am getting :

Attribute	Blank	Value
A		60
E		168
B		9'
A		42
E		143.5
A		42
E		143.5
A		56
E		167.7
B		8'
A		55
E		172
D		10000
B		16'
E		145
B		9' 3"
E		145
B		9' 3"
E		145
B		9' 3"

tamerj1

Re: Create a visualization in tabular form

tamerj1 — Mon, 08 Aug 2022 12:05:18 GMT

SK87
Ok, Please try the following and see if yu get some blank results in order to narrow down our search for the value that generates the error.

Value (Inch) = VAR String = Data[Value] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Value", VAR SingleItem = PATHITEM ( Items, [Value] ) VAR ItemLength = IF ( CONTAINSSTRING ( SingleItem, "'" ) || CONTAINSSTRING ( SingleItem, """" ), LEN ( SingleItem ) - 1, LEN ( SingleItem ) ) VAR Number = LEFT ( SingleItem, ItemLength ) RETURN IF ( CONTAINSSTRING ( SingleItem, "'" ), 12 * IFERROR ( VALUE ( Number ), BLANK () ), IFERROR ( VALUE ( Number ), BLANK () ) ) ) RETURN SUMX ( T2, [@Value] )

Re: Create a visualization in tabular form

SK87 — Mon, 08 Aug 2022 12:22:08 GMT

tamerj1

Thanks this is working fine.

But I don't want to convert the values of (A,B,D,E) columns to Foot, they should be in Inches only. Only column C should be converted to get MIN and MAX values but I need original values in the MIN /MAX for C as well

It should be

	MIN	MAX	Should be
A	0'	1446'4"	17356
B	0'	1041'8"	12500
C	0'	3721'10''	97 1/4"

Re: Create a visualization in tabular form

tamerj1 — Mon, 08 Aug 2022 12:40:06 GMT

SK87

Ok. I. Will check it out when I return to office

Re: Create a visualization in tabular form

tamerj1 — Mon, 08 Aug 2022 15:52:56 GMT

SK87
Here is the updated file as requested https://www.dropbox.com/t/GpKbc42ndoicPxR5

Value (Inch) = VAR String = Data[Value] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Value", VAR SingleItem = PATHITEM ( Items, [Value] ) VAR ItemLength = IF ( CONTAINSSTRING ( SingleItem, "'" ) || CONTAINSSTRING ( SingleItem, """" ), LEN ( SingleItem ) - 1, LEN ( SingleItem ) ) VAR Number = LEFT ( SingleItem, ItemLength ) RETURN IF ( CONTAINSSTRING ( SingleItem, "'" ), 12 * IFERROR ( VALUE ( Number ), BLANK () ), IFERROR ( VALUE ( Number ), BLANK () ) ) ) RETURN SUMX ( T2, [@Value] )

Min = VAR CurrentAttribute = SELECTEDVALUE ( Data[Attribute] ) VAR MinValue = MIN ( Data[Value (Inch)] ) VAR Foot = QUOTIENT ( MinValue, 12 ) VAR Inch = MOD ( MinValue, 12 ) RETURN IF ( CurrentAttribute = "B", IF ( Inch > 0, Foot & "' " & Inch & """", Foot & "'" ), MinValue )

Max = VAR CurrentAttribute = SELECTEDVALUE ( Data[Attribute] ) VAR MaxValue = MAX ( Data[Value (Inch)] ) VAR Foot = QUOTIENT ( MaxValue, 12 ) VAR Inch = MOD ( MaxValue, 12 ) RETURN IF ( CurrentAttribute = "B", IF ( Inch > 0, Foot & "' " & Inch & """", Foot & "'" ), MaxValue )

Mode = VAR T1 = ADDCOLUMNS ( Data, "@Frequency", CALCULATE ( COUNT (Data[Value] ), ALLEXCEPT ( Data, Data[Value (Inch)] ) ) ) VAR T2 = TOPN ( 1, T1, [@Frequency] ) VAR T3 = DISTINCT ( SELECTCOLUMNS ( T2, "@Value", [Value], "@ValueInch", [Value (Inch)] ) ) RETURN CONCATENATEX ( T3, [@Value], UNICHAR ( 10 ), [@ValueInch], ASC )

Re: Create a visualization in tabular form

SK87 — Mon, 08 Aug 2022 15:16:32 GMT

tamerj1 Thanks alot it worked!

I am trying to calculate Mode like MIN MAX but there is no direct function in DAX. It would be great if you could guide me on this as well.

Re: Create a visualization in tabular form

tamerj1 — Mon, 08 Aug 2022 15:52:07 GMT

SK87
The file link is update in the previous reply adding Mode measure.

Re: Create a visualization in tabular form

SK87 — Fri, 14 Oct 2022 16:08:49 GMT

Hi tamerj1

Thanks for your response above. I was trying to implement conditions (limits) for each attribute but results were not coming correct.

A (Min)> 2500

B (Min)>60 and (Max)B<350

C (Min)>5 and (Max)C<300

D (Min)>5 and (Max)D<220

E (Min)>25 and (Max)E<500

I applied for MIN but while implementing for Max the results not coming correct

Min =
VAR CurrentAttribute = SELECTEDVALUE ( Data[Attribute] )
VAR MinValue = MIN ( Data[Value (Inch)] )
VAR Foot = QUOTIENT ( MinValue, 12 )
VAR Inch = MOD ( MinValue, 12 )
RETURN

IF (

CurrentAttribute = "A",

IF (

MinValue > 1000,

MinValue & "lbs"

IF (

CurrentAttribute = "B",

IF (

MinValue > 60,

MinValue & " "" "

IF (

CurrentAttribute = "C",

IF (

MinValue > 5,

MinValue & " "" "

IF (

CurrentAttribute = "D",

IF (

MinValue > 5,

MinValue & " "" "

IF (

CurrentAttribute = "E",

IF (

MinValue > 25,

MinValue & " "" "

)

)))))

Please suggest

Re: Create a visualization in tabular form

SK87 — Mon, 17 Oct 2022 08:13:16 GMT

tamerj1 Any solution on below message