Create a visualization in tabular form

[SK87]

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.

[tamerj1 · Accepted solution]

@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 )

[SK87]

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

[SK87]

@tamerj1 Any solution on below message

[SK87]

@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.

[tamerj1]

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

[tamerj1]

@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] )

[SK87]

@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"

[tamerj1]

@SK87 

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

[tamerj1]

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 & "'"
    )

[SK87]

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

@amitchandak 

@tamerj1 
@Jihwan_Kim 
@SpartaBI 

@johnt75 

Thanks in advance

[SpartaBI]

@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



      

[SK87]

@SpartaBI This is text value

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

[tamerj1]

@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.

[SK87]

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