Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
Have Data as
Unit | Category
5 | Cat1,Cat2
3 | Cat2
Want Result as
Cat1 = 5
Cat2 = 8
Hi @yogeshmaney
I reckon you use the Split Column function in the Query Editor to split your Category column. Then unpivot the tables.
So
Back in Power BI you can then just drag the two columns to a grid and you should see your result
I'd recommend making a SUM measure as you develop your model but this should give you the idea,
Thanks For the reply!
But my Unit part is in Duration and converted as Measure and Column. Also the unpivot removes the other related data.
On Slicer Selection , it should show the Category graph
In Excel would have calculated with SUMPRODUCT(--($B$2:$C$3=F4)*$D$2:$D$3)
Thanks,
---Yogi
Hi @yogeshmaney
I'm not sure what you mean by "converted as Measure and Column".
The other option with Unpivot is to select the columns created by the Split Column and just select and unpivot those.
Hi,
The Unit part is in Duration (00:00:05 format), So I Converted it to Sec with New Measure and New Column.
Display Category Slipt On Select Slicer conditions
Regards,
Yogi
Was the unit part supposed to be a duration or simply a whole number/decimal value.
I agree with @Phil_Seamark, splitting the columns and then summing is the best way.
@ElliotP, I think there is other important data that we are not seeing in the sample supplied so difficult to understand the requirement without seeing the bigger picture.
Here's the Data Sample ,
The Brand Categories >Spitted with "," delimiter.
Duration of Avail > Converted to Sec with New Column
TCR> Trimmed to Minute Part
After Brand Categories split , Its spitted to two Col Category.1 and Category.2
When charted , considers only Category.1 , So giving Food value as 5 and Drinks as 3. Should give Food 5 and Drinks 8
Thanks,
Hi @yogeshmaney,
How about creating a new table using the following formula?
tablenew = SUMMARIZE ( ADDCOLUMNS ( FILTER ( CROSSJOIN ( SUMMARIZE ( Table2, [Duration of Avail], [Brand categories], "SubCnt", 1 + Len ( [Brand categories] ) - Len ( Substitute(Table2[Brand categories], ",", "" ) ) ), DummyTbl ), DummyTbl[Dummy] <= [SubCnt] ), "Brand", pathitem ( Substitute ( Table2[Brand categories], ",", "|" ), DummyTbl[Dummy] ) ), [Duration of Avail], [Brand] )
Reference:
http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html
Thanks,
Lydia Zhang
Great Thanks !
is there way to avoid blank row? Can link relationship between Calculated Table and main data
Hi @yogeshmaney,
Change DAX to:
tablenew = FILTER(SUMMARIZE ( ADDCOLUMNS ( FILTER ( CROSSJOIN ( SUMMARIZE ( Table2, [Duration of Avail], [Brand categories], "SubCnt", 1 + Len ( [Brand categories] ) - Len ( Substitute(Table2[Brand categories], ",", "" ) ) ), DummyTbl ), DummyTbl[Dummy] <= [SubCnt] ), "Brand", pathitem ( Substitute ( Table2[Brand categories], ",", "|" ), DummyTbl[Dummy] ) ), [Duration of Avail], [Brand] ), NOT(ISBLANK(Table2[Duration of Avail])))
What field you want to use to create relationship? The field of one table must have unique values when creating relationship.
Thanks,
Lydia Zhang
Hello, Tweaked little, Can't establish relation tablenew[Sr] and Table2[Sr.] ?
tablenew = FILTER(SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
Table2, [Sr.],
[DurationofAvail],
[BrandCategories],
"SubCnt",
1 + Len ( [Brandcategories] ) - Len ( Substitute(Table2[Brandcategories], ",", "" ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
),
"Brand",
pathitem (
Substitute ( Table2[Brandcategories], ",", "|" ),
DummyTbl[Dummy]
)
),[Sr.],
[DurationofAvail],
[Brand]
),
NOT(ISBLANK(Table2[Sr.])))
Hi @yogeshmaney,
You can use Brand categories field in the two tables to make relationship.
Thanks,
Lydia Zhang
Hi,
Please check the April Update . Option of Delimiter Split into rows.
Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |