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.
Hi Team,
I have Fact table (FactSalesTag) with one of column is stored as "|" seperated string. I want to create measure by splitting the column with "|" into seperate string on fly . Can someone please help with dax code for "UniqueTagMeasureCount" in "FactSalesTag" Table.
Example:
In below Table (FactSalesTag) , Tag is metadata info associated to ProductID over Time. If I split the Column into multiple rows then the SalesAmount will be duplicate hence I'm storing in single Column with "|" delimeter.
FactSales
Date | ProductID | SalesAmount |
11/1/2022 | 789 | 100 |
11/1/2022 | 567 | 200 |
12/1/2022 | 789 | 600 |
1/1/2023 | 567 | 300 |
2/1/2023 | 345 | 400 |
FactProductTagHistory
Date | ProductID | Tag |
11/1/2022 | 789 | ABC | DEF |
11/1/2022 | 567 | ABC |
12/1/2022 | 789 | GLK |
1/1/2023 | 567 | ABC | DEF |
2/1/2023 | 345 | GLK |
FactSalesTag ( Joined FactSales and FactProductTagHistory based on Date and ProductID)
Date | ProductID | Tag | SalesAmount |
11/1/2022 | 789 | ABC | DEF | 100 |
11/1/2022 | 567 | ABC | 200 |
12/1/2022 | 789 | GLK | 600 |
1/1/2023 | 567 | ABC | DEF | 300 |
2/1/2023 | 345 | GLK | 400 |
UniqueTagMeasureCount ==> Please help wiht DAX code for this measure
11/1/2022 | 2 (ABC, DEF) |
12/1/2022 | 1 (GLK) |
1/1/2023 | 2 (ABC, DEF) |
2/1/2023 | 1 (GLK) |
Thanks,
Abhiram
Solved! Go to Solution.
=
VAR _p =
CONCATENATEX ( VALUES ( FactSalesTag[Tag] ), 'FactSalesTag'[Tag], "|" )
VAR _t =
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( _p ) ),
"@tag", TRIM ( PATHITEM ( _p, [Value] ) )
)
RETURN
COUNTROWS ( SUMMARIZE ( _t, [@tag] ) )
=
VAR _p =
CONCATENATEX ( VALUES ( FactSalesTag[Tag] ), 'FactSalesTag'[Tag], "|" )
VAR _t =
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( _p ) ),
"@tag", TRIM ( PATHITEM ( _p, [Value] ) )
)
RETURN
COUNTROWS ( SUMMARIZE ( _t, [@tag] ) )
Hi @wdx223_Daniel - I'm using Visual Studio Editor and FactSalesTag is empty. I have processed model after deploying to server. Can you please help
......
RETURN
IF(_SplitData="",0,COUNTROWS(....))
Thanks @wdx223_Daniel for your response. I'm still getting same error. I can see the data Temp but when I drag related columns . example Date Dimension then it gives error as below. Is there any better way to debug this issue. Please let me know
BackEnd Query:
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Fiscal Month],
'Date'[Month Key],
"UniqueTagMeasureCount", [UniqueTagMeasureCount]
)
ORDER BY
'Date'[Fiscal Month] ASC,
'Date'[Month Key] ASC
Error:
The arguments in GenerateSeries function cannot be blank.
IF(_SplitData=""||_SplitData="|",0,COUNTROWS(....))
Hi @wdx223_Daniel - Thank you for your response. I'm getting below error when I drag the column from dimension Table ( ex: Month from Date Table , UniqueMeasureCount) . UniqueMeasureCount works fine but throwing error when I drag columns from related tables. Can you please help
Error:
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 |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |