Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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 )
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
@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.
@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] )
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" |
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 & "'"
)
Any solutions for above problem? Kindly help me anyone on this
@tamerj1
@Jihwan_Kim
@SpartaBI
Thanks in advance
@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.
@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.
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" |
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |