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 B2022-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 DateMinMaxA40204B8'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 differentPlease suggest.

Helper III

4 years ago

Solved

Create a visualization in tabular form

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

4 years ago

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 )

14 Replies

tamerj1

Community Champion

4 years ago

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
Helper III
4 years ago
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
  Community Champion
  4 years ago
  SK87
  The file link is update in the previous reply adding Mode measure.
SK87
Helper III
3 years ago
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
  Helper III
  3 years ago
  tamerj1 Any solution on below message

SK87
Helper III
4 years ago
Any solutions for above problem? Kindly help me anyone on this
amitchandak
tamerj1
Jihwan_Kim
SpartaBI
johnt75
Thanks in advance
- SpartaBI
  Community Champion
  4 years ago
  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
    Helper III
    4 years ago
    SpartaBI This is text value
    
    tamerj1 While converting the values to Inches I am getting below error as Column B value is Text

tamerj1

Community Champion

4 years ago

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

tamerj1

Community Champion

4 years ago

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
Helper III
4 years ago
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
  Community Champion
  4 years ago
  SK87
  Ok. I. Will check it out when I return to office

Forum Discussion

Create a visualization in tabular form

14 Replies

Related content

OLS with Tabular Editor - Breaking visuals

AAS - Tabular Editor 3 and Visual Studio

Merge cells in tabular visualization

Power BI - Deneb visual - Read a JSON to create the visuals

Tabular Editor - create multiple calculation items

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