topic Re: DAX to add a column to an existing table, assign category based on other column's data in DAX Commands and Tips

DAX to add a column to an existing table, assign category based on other column's data

p-ha — Mon, 13 Jul 2020 20:15:41 GMT

Hi, I have the sample table as follow.

Invoice #	Days Late	Days Late Bucket
A1	5	<=5 days
A2	10	>5 days
A3	1	<=5 days
A4	10	>5 days
A5	0	<=5 days
A6	5	<=5 days

Invoice # and Days Late are the 2 Data Columns exist on table, I do not have the Days Late Bucket Column and I'd like to add this to the table to assign the category of late bucket based on the condition of "Days Late". I can't do this via Query Editor as the datas are pulled via Analytic Services. Is there a Dax to achieve my purpose?

Ultimately I want to count the Invoices that associated to each Days Late Bucket. The analytic services have already created the Measure to calculate the invoice count for each Days Late, but not per Days Late Bucket.

In sum, current state: I can have a count table of:

Days Late - Count

0 - 1

1 - 1

5 - 2

10 - 2

I want to have a future table to show:

Days Late bucket - Count

<= 5 days - 4

> 5 days - 2

Thank you in advance for your help!!

P.Ha

Re: DAX to add a column to an existing table, assign category based on other column's data

lbendlin — Mon, 13 Jul 2020 20:26:35 GMT

This is in import mode, right? In that case you can add a calculated column with a switch statement, or you can create a group in the Power BI UI.

Re: DAX to add a column to an existing table, assign category based on other column's data

AlexisOlson — Mon, 13 Jul 2020 20:28:07 GMT

You can't modify tables if you're connected to an analysis server (not import but connected live).

In this case, a workaround might be to construct two measures, [<= 5 days] and [> 5 days] along these lines:

CALCULATE ( [InvoiceCountMeasure], Table1[Days Late] <=5 )

Re: DAX to add a column to an existing table, assign category based on other column's data

p-ha — Mon, 13 Jul 2020 21:01:52 GMT

Thanks lbendlin ! unfortunately this is a Connect Live datas.

Re: DAX to add a column to an existing table, assign category based on other column's data

p-ha — Mon, 13 Jul 2020 21:04:54 GMT

Thanks AlexisOlson! If I go with the 2 measures, I won't be able to display them in a pie chart or a table Graph right? My folks want to show for example : bucket < 5 days late X% and bucket > 5 days late Y% on a pie chart, but I can't see it can be achieved if I were to create 2 measures. Am I understanding it correctly?

Re: DAX to add a column to an existing table, assign category based on other column's data

AlexisOlson — Mon, 13 Jul 2020 21:20:23 GMT

Yeah, you can't use measures the way you can a table column (e.g. on a graph axis or as pie chart category) but you can at least use them in a matrix visual or a bar chart reasonably.