Cannot put a changing status on a table row

[IanR]

Something that looked as if it was going to be simple is turning out to be quite a challenge.

I would like to create a table visualisation from CRM data that contains three rows showing what happened to opportunities in a period. The rows will be Opened, Won and Lost. The columns of the table will be the periods involved with additional columns for comparison calculations, for example I will have columns marked this month, last month, percentage change, year to date this year, year to date last year, percentage change etc. The values will be counts and percentages. Sounds simple but I can’t work out how to get Opened, Won and Lost onto the rows. Unfortunately, these are not columns in the underlying opportunity table they are what happened to the opportunities in the periods. An opportunity could be in the Opened row for last month but be in the Won row for this month. As these statuses change depending on the period being examined and the columns in the underlying table are static I cannot use the columns that come with the data refresh or, as far as I can work out, any calculated columns for my table rows.

Do I have to write a separate measure for each cell in the table and then carefully stack up a pile of card visualisations, with one measure in each, so that the result looks like it’s actually a table? Even with this small number of rows and columns will be laborious and I’ll bet I won’t be able to get it to look exactly (or even vaguely) like the other tables in the report. If the number of rows was larger this approach would be impossible. Makes me think there has to be another way.

If it makes a difference the Won and lost measures will be using a date table/opportunity table relationship based on the opportunity close date, The Opened measure will be using a relationship based on opportunity opened date.

Am I missing something obvious?

Thanks

[IanR]

I think what I need to do is create a calculated ‘opportunity events’ table in DAX via a UNION function. This will effectively append Open events data from the Opportunities table to Won and Lost events data, also from the opportunities table. I still have a couple of questions:

Is there a way to stop ALL from bringing back only unique records, or is there another table function that can bring back selected columns without applying a DISTINCT?

Is there a way, in a function like ALL, to add a literal value to a column? Something like:

SELECT Column_A, ‘iteral value’ AS Column_B FROM Opportunities

Thanks
Ian

[v-yulgu-msft · Accepted solution]

Hi @IanR,

---

 Is there a way to stop ALL from bringing back only unique records, or is there another table function that can bring back selected columns without applying a DISTINCT?

---

Actually, when combine two tables (some selected columns) into a single one, it will return all data rows rather than return distinct values. So, in your scenario, not sure how you created the calculated table via UNION function, please provide sample data of source tables. According to current description, it's hard to imagine your table structure and not very clear about your requirement. It would be better you could elaborate your scenario with some examples.

---

 Is there a way, in a function like ALL, to add a literal value to a column? Something like:

SELECT Column_A, ‘iteral value’ AS Column_B FROM Opportunities

---

Does this meet your requirement?

Table =
UNION (
    SELECTCOLUMNS (
        Table1,
        "Col1", Table1[Table1_col1],
        "Col2", Table1[Table_col2]
    ),
    SELECTCOLUMNS ( Table2, "Col1",Table2[Table2_col1], "Col2", "iteral value" )
)

Best regards,
Yuliana Gu

---

[IanR]

UNION and SELECTCOLUMNS.

Perfect.