Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Power BI master around the world,
I have been so active recently because getting interested with this DAX. I have a very difficult measure that i am not sure if it is possible to do in Power BI.
Let say i have this table
Column Name |
Hydrogenxxx, Lithiumyyy, Natriumzzz |
Hydrogenaaa, Natriumbbb, Natriumccc |
Hydrogenaaa |
Lithium<ddd |
Natriumccc |
Natriumeee |
Lithiumfff |
So there are blank rows.
I would like to identify how many times occurence of Natrium in this column.
1st row : 1
2nd row : 2
6th row : 1
7th row : 1
There is "," (comma) delimeter between each string.
Any idea? Thanks
Solved! Go to Solution.
Hi @dannytan1112
Please refer to attached sample file with the solution
Count =
VAR CurrentElelent = SELECTEDVALUE ( Elements[Element] )
RETURN
SUMX (
VALUES ( 'Table'[Column Name] ),
VAR String = 'Table'[Column Name]
VAR Items = SUBSTITUTE ( String, ", ", "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 = FILTER ( T2, CONTAINSSTRING ( [@Item], CurrentElelent ) )
RETURN
COUNTROWS ( T3 )
)
you may also create a column like this:
COUNT = (LEN([Column Name]) - LEN(SUBSTITUTE([Column Name], "Natrium", "")))/7
tried and it worked like this:
Hi @dannytan1112
Please refer to attached sample file with the solution
Count =
VAR CurrentElelent = SELECTEDVALUE ( Elements[Element] )
RETURN
SUMX (
VALUES ( 'Table'[Column Name] ),
VAR String = 'Table'[Column Name]
VAR Items = SUBSTITUTE ( String, ", ", "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 = FILTER ( T2, CONTAINSSTRING ( [@Item], CurrentElelent ) )
RETURN
COUNTROWS ( T3 )
)
Hi @tamerj1
Ihave 2 table in my Power BI report B-Details and SPResult. SP result table is a stored procedure and will change dynamically with parameters. I want to create a new table from this in which it should filter Name and Country from B-Details and SPResult. Now after filtering this we have a uniquekey in B-Details. Now in the new table for each UniqueKey we have to get all the filtered values from SPResult table. So in the new table UniqueKey key row will be duplicated with as much rows we have in SPResult. Can you help me how to fix this?
Hi @tamerj1
The Table Name PlanDetails:
SupplierCode | SupplierName | Season | DepatmentCode | DepartmentName | ProductionTypeCode | ProductTypeName | SectionCode | CountryCode | CountryName | GeographicalCountryID | GroundValue | SupplierPlan | MonthlyPlan | GroundSpread | PlanVersion | PlanStatus | PlanVersionType | DataBaseItemPK | DataBaseStatus | FromSMAPPBackBone | DepartmentID | ProductionGroupID | CorportateBrandCode | Division | PeriodNum | UniPeriodKey | PeriodValue% | PeriodValue | UnikeyBD-SP | UniqueKey |
1234 | xyz | 9 | 3030 | EQ & Local Campaigns | 63 | Dresses | 115 | ID | Indonesia | 1223 | 0 | 10000 | Sep 2023:0;Oct 2023:0;Nov 2023:5007;Dec 2023:4993;Jan 2024:0;Feb 2024:0;Mar 2024:0;Apr 2024:0;May 2024:0;Jun 2024:0; | 3 | Saved | Updated | 48304 | Yes | No | 945 | 10 | 1 | 103 | 0 | 115630 | 1.12 | 112 | 10310 | 1148743030115ID103 | |
12345 | zzz | 9 | 3090 | Global Campaigns | 63 | Dresses | 115 | TR | Türkiye | 1171 | 0 | 90100 | Sep 2023:0;Oct 2023:256.55071628251005;Nov 2023:2352.6083887178706;Dec 2023:4453.429160366789;Jan 2024:10072.229928317362;Feb 2024:14900.493435436674;Mar 2024:17779.051863857374;Apr 2024:24495.031555589398;May 2024:14322.335516082363;Jun 2024:1468.2792359653226; | 3 | Saved | Updated | 38590 | Yes | No | 175 | 10 | 1 | 103 | 0 | 115630 | 1.12 | 1009.12 | 10310 | 1015413090115TR103 |
Hi @tamerj1
The 2nd Table is StoredProcedureresult that changes when parameters are changed
season | MOTcode | MOTname | countryofproduction | planningmarketcode | productionGroupId | ProductionTypeCode | InshopWeek | PeriodNum | Division | percentageVal | LLT | InshopWeek - Copy | ISW-Year | ISW-Week | TOD | TODMonth |
7 | 1 | Sea | IN | 1026 | 10 | 63 | 202350 | 0 | 103 | 0.562 | 6 | 202350 | 2023 | 50 | 202344 | 2023-10 |
7 | 1 | Sea | IN | 1011 | 10 | 63 | 202350 | 0 | 103 | 0.446 | 8 | 202350 | 2023 | 50 | 202342 | 2023-10 |
7 | 1 | Sea | IN | 1006 | 10 | 63 | 202350 | 0 | 103 | 0.414 | 7 | 202350 | 2023 | 50 | 202343 | 2023-10 |
7 | 1 | Sea | ID | 1011 | 10 | 63 | 202351 | 0 | 103 | 1.296 | 9 | 202351 | 2023 | 51 | 202342 | 2023-10 |
7 | 1 | Sea | MM | 1026 | 10 | 63 | 202349 | 0 | 103 | 6.726 | 7 | 202349 | 2023 | 49 | 202342 | 2023-10 |
Hi @tamerj1 I have sent the Two table data, Here the tables are filtered based on ProductionTypecode and PeriodNum from both tables, after filtering each row from PlanDetails has a UniqueKey row, This should result in a table where each UniqueKey row from "PlanDetails" is duplicated with all the data from "Bought Details" and combined with the filtered data from "StoredprocedureResult" for each row based on the specified conditions(from Stored Procedure, the columns we need from SP result are MOTcode MOTname planningmarketcode InshopWeek percentageVal LLT InshopWeek - Copy ISW-Year ISW-Week TOD TODMonth)
This is the dax that I tried but it's not working.
CombinedTable =
VAR ProductionType = FILTER(RELATEDTABLE('MarketPlan'), 'MarketPlan'[ProductionTypeCode])
VAR PeriodNO = FILTER(RELATEDTABLE('MarketPlan'), 'MarketPlan'[PeriodNum])
VAR FilteredBD = FILTER(ALL('SP_Result'),
'SP_Result'[ProductionTypeCode] = ProductionType &&
'SP_Result'[PeriodNum] = PeriodNO
)
VAR UniqueKeys = SUMMARIZE('MarketPlan', 'MarketPlan'[UniqueKey])
RETURN
ADDCOLUMNS(
FILTER(UniqueKeys, TRUE),
"MarketPlanData", SUMMARIZE(
'MarketPlan',
'MarketPlan'[UniqueKey],
'MarketPlan', // Include all columns from 'MarketPlan'
'SP_Result'[modeoftransportcode],
'SP_Result'[modeoftransportname],
'SP_Result'[planningmarketcode],
'SP_Result'[planningmarketname],
'SP_Result'[InshopWeek],
'SP_Result'[percentageVal]
)
)
*If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
19 | |
18 | |
18 | |
15 |
User | Count |
---|---|
38 | |
22 | |
18 | |
15 | |
13 |