Forum Discussion
Microsoft EmployeeDAX distinct count measure on Pipe separated string
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
=
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] ) )
6 Replies
- wdx223_Daniel
Community Champion
=
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] ) )
abhiram342Hi 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
Error:
- abhiram342
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
- wdx223_Daniel
......
RETURN
IF(_SplitData="",0,COUNTROWS(....))