Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, everyone.
I suppose that it is noobie question - but I can't find an answer. Pls, help.
I have 2 tables and need to create a new one
The first table contains Client ID and Source
The second contains Client ID - and Sold
New one - Sold Cliend ID + All Souces (separated by commas).
Which function can help with this?
The logic is
IF SOURCE.table [Client ID] = SOLD.table [Client ID]
AND SOLD.table [Sold] = yes
RETURN SOURCE.Sold [Client ID], LIST OF SOURCES (??)
Thank you!
Solved! Go to Solution.
Hi @Anonymous ,
You can create the following table:
Table =
ADDCOLUMNS (
SUMMARIZE ( FILTER ( Sold; Sold[Sold] = "YES" ); Sold[ClientID] );
"Source"; CONCATENATEX ( RELATEDTABLE ( Source ); Source[Source]; ", " )
)
If you want a measure you should use:
Measure = CONCATENATEX(Source;Source[Source]; ", ")
Then just filter out the Sold to yes on the filter of the visual.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello I have a question related to this topic. I have a table and measure created like this.
But what I want is to have only 1 value in MCC_LIST. Just filter distinct values, no repetition.
Thank you for help, Dusan
HI @dmatliak ,
Instead of refering to the all table refer only to the columns you need that will create a summarize with distinct values:
MCCList =
IF (
HASONEVALUE ( LOC[CUSTID] ),
VAR mcc_list =
VALUES ( LOC[CUSTID] )
RETURN
CONCATENATEX ( FILTER ( ALL ( LOC[CUSTID], LOC[MCC] ), LOC[CUSTID] IN mcc_list ), LOC[MCC], "," ),
BLANK ()
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
check this Power Query Solution out.
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @Anonymous ,
You can create the following table:
Table =
ADDCOLUMNS (
SUMMARIZE ( FILTER ( Sold; Sold[Sold] = "YES" ); Sold[ClientID] );
"Source"; CONCATENATEX ( RELATEDTABLE ( Source ); Source[Source]; ", " )
)
If you want a measure you should use:
Measure = CONCATENATEX(Source;Source[Source]; ", ")
Then just filter out the Sold to yes on the filter of the visual.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsJoin both the tables on client id and use concatenatex on sold.table[source]
https://docs.microsoft.com/en-us/dax/concatenatex-function-dax
all source =concatenatex(sold.table[source])
Display it in a table or matrix along with client id
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Concatenate( First part, Concatenate( ", ", Second part))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |